MySQL Forums
Forum List  »  French

Re: Utilisation des tables d'index sur select * ... order by ...
Posted by: Jean Molliné
Date: September 22, 2010 05:42AM

Salut,
déjà au niveau des index, dans la mesure où une colonne fait partie de la clé primaire, elle est d'ores et déjà indexée, il est donc totalement inutile de créer un index supplémentaire. L'index UNIQUE est inclus dans la clé primaire, donc là aussi, la création d'un index UNIQUE sur la paire de colonnes est inutile.

J'ajoute quand même qu'il est préférable d'éviter d'utiliser des mots réservés du SQL pour nommer des colonnes, donc « NUMBER» et «STATE» sont à proscrire pour éviter des conflits et/ou des résultats pour le moins étranges lors des requêtes qui n'auraient pas de caractères d'échappement autour des noms de ces colonnes.

Ensuite, le principe général de base d'une clé primaire, c'est que c'est à considérer comme une donnée système du serveur de base de données, il est par conséquent vivement recommandé d'utiliser une colonne auto-incrémentée, même si on veut en outre avoir des colonnes avec des index UNIQUES. Mais les colonnes qui sont des données « significatives » de l'application ne devraient jamais être utilisées en clé primaire : ça évite les risques d'erreur par manipulation des valeurs, on laise le SGBD gérer ces clés.
Pour ma part, je modifierais le code de création de la manière suivante :
CREATE TABLE IF NOT EXISTS `MYBASE`.`MYTABLE` (
  `MYTABLE_ID` INT(11) NOT NULL AUTO_INCREMENT,
  `NUMBER` INT(11) NOT NULL ,
  `NAME` INT(11) NOT NULL ,
  `STATE` INT(11) NOT NULL ,
PRIMARY KEY (`MYTABLE_ID`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;

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

Enfin, si les index n'apparaissent pas dans le résultat de la requête EXPLAIN, c'est qu'aucune jointure n'est faite, par conséquent, aucun index n'est utilisé. Supposons maintenant la requête suivante :
EXPLAIN SELECT * 
FROM MYTABLE t1
    INNER JOIN MYTABLE t2 ON t1.`MYTABLE_ID` = t2.`MYTABLE_ID`
WHERE t1.`NAME` LIKE '%toto%' 
ORDER BY t1.`NUMBER`, t1.`NAME`;
Nous aurons alors le résultat suivant :
+----+-------------+-------+--------+---------------+---------+---------+----------------------+------+-----------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref                  | rows | Extra                       |
+----+-------------+-------+--------+---------------+---------+---------+----------------------+------+-----------------------------+
|  1 | SIMPLE      | t1    | ALL    | PRIMARY       | NULL    | NULL    | NULL                 |    1 | Using where; Using filesort |
|  1 | SIMPLE      | t2    | eq_ref | PRIMARY       | PRIMARY | 4       | mybase.t1.MYTABLE_ID |    1 |                             |
+----+-------------+-------+--------+---------------+---------+---------+----------------------+------+-----------------------------+
Si je modifie un peu les conditions de jointure comme ceci :
EXPLAIN SELECT * 
FROM MYTABLE t1
    INNER JOIN MYTABLE t2 ON t1.`NAME` = t2.`NAME`
WHERE t1.`NAME` LIKE '%toto%' 
ORDER BY t1.`NUMBER`, t1.`NAME`;
Le résultat sera le suivant :
+----+-------------+-------+------+-----------------+-----------------+---------+----------------+------+-----------------------------+
| id | select_type | table | type | possible_keys   | key             | key_len | ref            | rows | Extra                       |
+----+-------------+-------+------+-----------------+-----------------+---------+----------------+------+-----------------------------+
|  1 | SIMPLE      | t1    | ALL  | idx_name_number | NULL            | NULL    | NULL           |    1 | Using where; Using filesort |
|  1 | SIMPLE      | t2    | ref  | idx_name_number | idx_name_number | 4       | mybase.t1.NAME |    1 |                             |
+----+-------------+-------+------+-----------------+-----------------+---------+----------------+------+-----------------------------+
Les valeurs affichées comme possible_keys a changé... par rapport à la nouvelle condition de jointure.

Est-ce que ça répond à la question ?

______________________________________________________________
Une question bien formulée, c'est un problème bien compris : ça représente déjà les 3/4 de la réponse ;)

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Utilisation des tables d'index sur select * ... order by ...
2100
September 22, 2010 05:42AM


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.