[Toolserver-l] I have difficulties with USE INDEX(...) in mysql on the toolserver.

[philipp.zedler at tu-berlin.de]

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.sql
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)