Thank you Sasha for your answer.0
I still miss-understand index and 'order by'.
After creating multiples index on a 'Test' table, the current problem
is that mysql optimizer doesn't choose the right index.
Here is a small example
mysql> desc Test ;
+-----------------+-------------+------+-----+---------+
| Field | Type | Null | Key | Default |
+-----------------+-------------+------+-----+---------+
| JobStatus | varchar(32) | YES | MUL | NULL |
| SubmitDate | datetime | YES | | NULL |
| SpawnDate | datetime | YES | | NULL |
| TerminationDate | datetime | YES | | NULL |
+-----------------+-------------+------+-----+---------+
with 3 multiple indexes :
KEY K00(JobStatus, SubmitDate)
KEY K01(JobStatus, SpawnDate)
KEY K02(JobStatus, TerminationDate)
When I run a request like : select ... order by SubmitDate
Mysql doesn't choose the right index (see Using filesort needed)
mysql> explain select * from Test \
where (JobStatus = 'RUNNING') order by SubmitDate ;
+-------+------+---------------+------+---------+-------+-------+-----------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra
|
+-------+------+---------------+------+---------+-------+-------+-----------------------------+
| Test | ref | K00,K01,K02 | K02 | 33 | const | 20600 | Using where; Using
filesort |
+-------+------+---------------+------+---------+-------+-------+-----------------------------+
if I force the index choice with : use index (K00), I got :
mysql> explain select * from Test use index (K00) \
where (JobStatus = 'RUNNING') order by SubmitDate ;
+-------+------+---------------+------+---------+-------+-------+-------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+------+---------------+------+---------+-------+-------+-------------+
| Test | ref | K00 | K00 | 33 | const | 30084 | Using where |
+-------+------+---------------+------+---------+-------+-------+-------------+
I understand that the fisrt choice is done according to
the number of rows to examine (20600 vs 30084), but
I checked, with a small external program, that
the second solution (use index (K00)) is faster (~ x2);
I try to enforce the optimizer by setting max-seeks-for-key
but no result !
What is wrong with my indexes ?
Regards
--
Bernard CHAMBON
IN2P3 / CNRS (Centre de Calcul de LYON)
email: mailto:[EMAIL PROTECTED]
Tel : 04 72 69 42 18
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
