Hallo,

I'm working with the very huge mysql table 'revision' in the database e.g. enwiki_p and would like to choose keys by hand using 'USE INDEX(...)', because I think, mysql's choices are sometimes not ideal.

My difficulty is that if I try to do so, mysql claims that the key __doesn't exist__ although it is displayed when I use 'EXPLAIN' and it exists according to the database scheme.

Is there something special about the mysql implementation on the toolserver?

Thanks for any hints. Philipp

NOW THE DETAILS:

To display that 'rev_timestamp' is in fact a key in the table 'revision', we can do this: mysql> EXPLAIN SELECT rev_timestamp FROM revision LIMIT 10; +----+-------------+----------+-------+---------------+---------------+---------+------+-----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+-------+---------------+---------------+---------+------+-----------+-------------+ | 1 | SIMPLE | revision | index | NULL | rev_timestamp | 16 | NULL | 438608433 | Using index | +----+-------------+----------+-------+---------------+---------------+---------+------+-----------+-------------+ 1 row in set (0.00 sec)

Also from this website http://svn.wikimedia.org/svnroot/mediawiki/trunk/phase3/maintenance/tables.s... one can conclude that the index 'rev_timestamp' is there.

However: If I try to set the key by hand, mysql claims the key would not exist (also if I omit the quote signs): mysql> SELECT rev_timestamp FROM revision USE INDEX(`rev_timestamp`) LIMIT 10; ERROR 1176 (42000): Key 'rev_timestamp' doesn't exist in table 'revision'

Also in the description of the table there appear no keys: mysql> DESCRIBE revision; +----------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------+---------------------+------+-----+---------+-------+ | rev_id | int(8) unsigned | NO | | 0 | | | rev_page | int(8) unsigned | NO | | 0 | | | rev_text_id | int(8) unsigned | NO | | 0 | | | rev_comment | varbinary(255) | YES | | NULL | | | rev_user | int(5) unsigned | NO | | 0 | | | rev_user_text | varbinary(255) | NO | | | | | rev_timestamp | varbinary(14) | NO | | | | | rev_minor_edit | tinyint(1) unsigned | NO | | 0 | | | rev_deleted | tinyint(1) unsigned | NO | | 0 | | | rev_len | int(8) unsigned | YES | | NULL | | | rev_parent_id | int(8) unsigned | YES | | NULL | | +----------------+---------------------+------+-----+---------+-------+ 11 rows in set (0.00 sec)