Skip navigation links

MySQL Forums :: French :: Utilisation des tables d'index sur select * ... order by ...


Advanced Search

Utilisation des tables d'index sur select * ... order by ...
Posted by: pa house ()
Date: September 22, 2010 03:10AM

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)

Options: ReplyQuote


Subject Views Written By Posted
Utilisation des tables d'index sur select * ... order by ... 3273 pa house 09/22/2010 03:10AM
Re: Utilisation des tables d'index sur select * ... order by ... 1451 Jean Molliné 09/22/2010 05:42AM
Re: Utilisation des tables d'index sur select * ... order by ... 1726 pa house 09/23/2010 03:34AM
Re: Utilisation des tables d'index sur select * ... order by ... 1831 Jean Molliné 09/23/2010 04:28AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.