MySQL Forums
Forum List  »  French

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 ...
3871
September 22, 2010 03:10AM


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.