Utilisation des tables d'index sur select * ... order by ...

[pa house]

Je cree un table avec une cle primary comprenant 2 champs (number et name)
puis un index utilisant name+index.

aussi je m'attend a ce que ces 2 queries soient optimisees via la primary ou un index.

NON la premiere est optimisee via la primary:
explain select * from MYTABLE order by NUMBER,NAME limit 100000,2;

La seconde utilise un full scan:
explain select * from MYTABLE order by NAME,NUMBER limit 100000,2;

Ci après:
========
la description de la table (reduite au plus simple)
les 2 resultats des explain select *
Server version: 5.1.49-1-log (Debian)


Merci pour votre explication!!!





Table:
============================================================================

create database MYBASE;

DROP TABLE IF EXISTS `MYBASE`.`MYTABLE` ;

CREATE TABLE IF NOT EXISTS `MYBASE`.`MYTABLE` (
`NUMBER` INT(11) NOT NULL ,
`NAME` INT(11) NOT NULL ,
`STATE` INT(11) NOT NULL ,
PRIMARY KEY (`NUMBER`, `NAME`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


CREATE INDEX `idx_number` ON `MYBASE`.`MYTABLE` (`NUMBER` ASC) ;
CREATE INDEX `idx_name` ON `MYBASE`.`MYTABLE` (`NAME` ASC) ;
CREATE UNIQUE INDEX `idx_name_number` ON `MYBASE`.`MYTABLE` (`NAME` ASC, `NUMBER` ASC) ;




RESULT
======================================================================


mysql> use MYBASE;
Database changed
mysql> explain select * from MYTABLE order by NUMBER,NAME limit 100000,2;
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------+
| 1 | SIMPLE | MYTABLE | index | NULL | PRIMARY | 8 | NULL | 1 | |
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------+
1 row in set (0.00 sec)

mysql> explain select * from MYTABLE order by NAME,NUMBER limit 100000,2;
+----+-------------+---------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+----------------+
| 1 | SIMPLE | MYTABLE | ALL | NULL | NULL | NULL | NULL | 1 | Using filesort |
+----+-------------+---------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)