Très simple requête mysql de ne pas utiliser d'index

Tri de ma table mySQL n'utilise pas l'index et je ne sais pas pourquoi.

J'ai:

CREATE TABLE IF NOT EXISTS `test` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  KEY `kk` (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

et ce:

EXPLAIN SELECT * 
FROM test
ORDER BY a

ainsi que ce

EXPLAIN SELECT * 
FROM test
USE INDEX ( kk ) 
ORDER BY a

me donne ceci:

id select_type table type possible_keys key  key_len ref  rows  Extra
1  SIMPLE      test  ALL  NULL          NULL NULL    NULL 10009 Using filesort

J'aimerais ne pas voir cette filesort, et l'utilisation de la touche kk pour trier mon tableau. Ce que je fais mal?

Merci pour vos messages les gars, ils ont répondu à ma question! Cependant, maintenant, je ne pas comprendre ce que l'on entend par "analyse de la table" et "filesort"? Même si je suis sélectionner tous les champs et toutes les lignes d'une table, n'est-il pas plus rapide de trier la table par une colonne en marche en O(n) l'arbre interne de l'index de la colonne (et puis en regardant dans le fichier de table les colonnes supplémentaires demandés, en O(1) pour chaque ligne => le fichier d'index stocke chaque ligne de la position dans le fichier de table, ou?), que de trier par exemple, par le rapide de tri en O(n * log n) (potentiellement) de façon aléatoire stockées lignes dans le fichier de la table, sans toucher à l'index? Je suppose que ma compréhension de la façon dont les indices de travailler dans mySQL est faux.

InformationsquelleAutor captain lizard | 2013-07-17
  1. 16
    1. Vous êtes en sélectionnant toutes les lignes
    2. Vous êtes à la sélection de toutes les colonnes

    Suivant ce que j'ai dit ci-dessus - mysql estime qu'il sera plus efficace d'utiliser le scan complet.

    Pour obtenir de l'aide de l'index, vous devez ajouter quelques WHERE qui permettrait de limiter à un nombre raisonnable de lignes retournées (par exemple 50)

    • J'ai probablement dire "l'ensemble de la table" (ou "toutes les lignes", en effet). Pas sûr de savoir comment il exprimer en anglais dans une manière correcte.
    • Oui, "l'ensemble de la table" ou "toutes les lignes" serait mieux je pense.
    • Dois-je "ET" #1 et #2 ou "OU" eux"?
    • c'est à la fois, de sorte que "et"
    InformationsquelleAutor zerkms
  2. 5

    @zerkms est correct, par la lecture de toutes les lignes dans la table MySQL décide qu'elle va avoir à lire la majorité de la table, de toute façon, donc il n'y a pas besoin de lire l'index. L'optimiseur de changements de comportement si vous sélectionnez un sous-ensemble de la table.

    Par exemple, j'ai créé un tableau comme le vôtre et rempli avec de l'16384 lignes, avec des entiers aléatoires entre 0 et 1000000. J'ai essayé de l'EXPLIQUER pour les différents sous-ensembles de la table, d'abord 15% de la table, puis, à 17%, de 19%.

    mysql> EXPLAIN SELECT *  FROM test where a < 150000 ORDER BY a;
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test  | range | kk            | kk   | 5       | NULL | 2272 | Using where |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
mysql> EXPLAIN SELECT *  FROM test where a < 170000 ORDER BY a;
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test  | range | kk            | kk   | 5       | NULL | 2560 | Using where |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
mysql> EXPLAIN SELECT *  FROM test where a < 190000 ORDER BY a;
+----+-------------+-------+------+---------------+------+---------+------+-------+-----------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra                       |
+----+-------------+-------+------+---------------+------+---------+------+-------+-----------------------------+
|  1 | SIMPLE      | test  | ALL  | kk            | NULL | NULL    | NULL | 16384 | Using where; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+-------+-----------------------------+

    Vous pouvez également convaincre d'utiliser l'index par la diminution de l'colonnes jusqu'à ce que vous êtes juste en sélectionnant les colonnes de l'index. Il va décider de lecture de l'index seul, et de ne pas toucher la table. Vous pouvez définir un index avec des colonnes supplémentaires si vous en avez besoin, même si les colonnes ne sont pas nécessaires pour la recherche ou de tri.

    mysql> ALTER TABLE test ADD KEY kk2 (a,b);
mysql> EXPLAIN SELECT a,b FROM test ORDER BY a;
+----+-------------+-------+-------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | test  | index | NULL          | kk2  | 10      | NULL | 16384 | Using index |
+----+-------------+-------+-------+---------------+------+---------+------+-------+-------------+
    InformationsquelleAutor Bill Karwin
  3. 1

    Puisque vous n'avez pas de clause where, il va faire une filesort (table scan), à moins que le seul élément que vous choisissez est à partir de l'index. Cette requête va utiliser l'index. Voir ce SQL Violon

    EXPLAIN SELECT a FROM test ORDER BY a

    Toutefois, si vous sélectionnez une colonne qui n'a pas dans l'index (* ou b), il va faire une analyse de fichier. Ajouter une clause where avec un index de couverture ou de modifier les colonnes sélectionnées.

    InformationsquelleAutor kheld