MySQL : optimisation MySQL : sur le "order by"

Voilà une manière efficace d’accélérer de manière hallucinante les requêtes qui ont, dans le résultat explain, un extra qui est : USING filesort. Cet extra ralenti énormément la requête.

La solution est très simple : les colonnes de la clause ORDER BY doivent se retrouver dans le WHERE : voilà, dans l’exemple qui suit, col1 et col3 qui sont des indexes. S’ils n’apparaissent pas des deux côtés, alors, il y a l’horrible USING filesort :


mysql> EXPLAIN SELECT * FROM testtab
WHERE col1 > 50000 AND col1 < 150000 ORDER BY col3;
Extra: USING WHERE; USING filesort
mysql> EXPLAIN SELECT * FROM testtab
WHERE col1 > 50000 AND col3 < 20000 ORDER BY col3;
Extra: USING WHERE
mysql> EXPLAIN SELECT * FROM testtab
WHERE col3 < 20000 AND col1 > 50000 ORDER BY col3;
Extra: USING WHERE
mysql> EXPLAIN SELECT * FROM testtab WHERE col3 < 20000
ORDER BY col3;
Extra: USING WHERE
mysql> EXPLAIN SELECT * FROM testtab WHERE col3 < 20000
ORDER BY col1;
Extra: USING WHERE; USING filesort
mysql> EXPLAIN SELECT * FROM testtab WHERE col1 < 20000
ORDER BY col1;
Extra: USING WHERE
mysql> EXPLAIN SELECT * FROM testtab WHERE col1 < 20000
ORDER BY col3;
Extra: USING WHERE; USING filesort

3 comments

  1. papattes

    Bonjour,
    je suis novice en la matière mais après quelque test avec une BDD comportant ~20000 ligne, j’obtiens un simple USING_WHERE dans mon EXPLAIN rien qu’en mettant de bon index « multicolonne » et dans l’ordre.
    Ex :
    EXPLAIN SELECT * FROM testtab WHERE col1 USING_WHERE
    Avec l’index NOM_DE_L_INDEXE : col3,col1 => USING_FILESORT

    comme je le disais, je suis novice, ais-je de la chance sur ce coup la ?

  2. papattes

    Bonjour,
    je suis novice en la matière mais après quelque test avec une BDD comportant ~20000 lignes, j’obtiens un simple USING_WHERE dans mon EXPLAIN rien qu’en mettant de bon index « multicolonnes » et dans l’ordre.
    Ex :


    EXPLAIN SELECT * FROM testtab WHERE col1 < 20000
    ORDER BY col3;

    Avec l’index NOM_DE_L_INDEXE : col3,col1 => USING_FILESORT
    Avec l’index NOM_DE_L_INDEXE : col1,col3 => USING_WHERE

    comme je le disais, je suis novice, ais-je de la chance sur ce coup la ?

Poster un commentaire

Vous devriez utiliser le HTML:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>