Requête MySQL Slow JOIN lors de l'utilisation de ORDERBY

Le SELECT * que vous avez dans votre requête est moche, et cela peut souvent être un tueur d'index. Cela peut empêcher l'utilisation d'un index, car la plupart des index que vous définiriez ne couvriraient pas toutes les colonnes demandées par SELECT * . L'approche de cette réponse est d'indexer toutes les autres tables de votre requête, ce qui inciterait donc MySQL à ne faire qu'une seule analyse de la table article .

CREATE INDEX idx1 ON article_to_ressort (article_id, ressort_id);
CREATE INDEX idx2 ON smartressort_to_ressort (ressort_id, smartressort_id);

Ces deux indices devraient accélérer le processus de jonction. Notez que je n'ai pas défini d'index pour la table smartressort , en supposant que sa colonne id est déjà une clé primaire. J'écrirais probablement votre requête en commençant par la table article et en la rejoignant vers l'extérieur, mais cela ne devrait pas vraiment avoir d'importance.

De plus, forcer un index est généralement une mauvaise idée ou non nécessaire. L'optimiseur peut généralement déterminer quand il est préférable d'utiliser un index.

CHOISIR beaucoup de colonnes DANS les tables ORDER PAR quelque chose LIMIT quelques est un anti-modèle de performance notoire; il doit récupérer et ordonner tout un désordre de lignes et de colonnes, juste pour supprimer toutes les lignes sauf quelques lignes du jeu de résultats.

L'astuce consiste à déterminer les valeurs de article.id dont vous avez besoin dans votre jeu de résultats, puis à récupérer uniquement ces valeurs. Cela s'appelle une jointure différée .

Cela devrait vous donner cet ensemble de valeurs id . Il n'est probablement pas nécessaire de rejoindre la table smartressort car smartressort_to_ressort contient les valeurs id dont vous avez besoin.

SELECT a.*
  FROM article a
 WHERE a.id IN (
                 SELECT a.id
                   FROM article a
                   JOIN article_to_ressort atr ON a.id = atr.article_id
                   JOIN smartressort_to_ressort str ON atr.ressort_id = str.ressort_id
                  WHERE str.smartressort_id = 1
                  ORDER BY a.created_at DESC
                  LIMIT 25
               )
 ORDER BY a.created_at DESC

En plus de la bonne réponse de @TimBiegelsen, je recommanderais de modifier votre index source_created :

...
KEY `source_created` (`id`, `created_at`)

Le gain serait que MySQL pourrait l'utiliser pour tri, et n'aurait pas besoin de récupérer les 16406 lignes. Cela peut aider ou non, mais cela vaut la peine d'essayer (peut-être avec une déclaration explicite pour l'utiliser)

Pour commencer: vous pouvez supprimer la table smartressort de votre requête, car elle n'y ajoute rien.

Voici votre requête réécrite. Nous voulons tous les ressorts pour smart ressort # 1 puis tous les articles pour ces ressorts. Parmi ceux-ci, nous montrons le plus récent 25.

CREATE INDEX idx1 ON smartressort_to_ressort (smartressort_id, ressort_id);
CREATE INDEX idx2 ON article_to_ressort (ressort_id, article_id);
CREATE INDEX idx3 ON article (id, created_at);

Maintenant, quels index seraient nécessaires pour aider le SGBD avec cela? Commencez par la table interne ( smartressort_to_ressort ). Nous accédons à tous les enregistrements avec un smartressort_id donné et nous voulons obtenir le ressort_id associé. L'index doit donc contenir ces deux colonnes dans cet ordre. Idem pour article_to_ressort et son ressort_id et article_id . Enfin, nous voulons sélectionner les articles en fonction des identifiants des articles trouvés et les classer par created_at .

SELECT *
FROM article
WHERE id IN
(
  SELECT article_id
  FROM article_to_ressort 
  WHERE ressort_id IN
  (
    SELECT ressort_id
    FROM smartressort_to_ressort
    WHERE smartressort_id = 1
  )
)
ORDER BY created_at DESC
LIMIT 25;

Quoi qu'il en soit, ces index ne sont qu'une offre à le SGBD. Il peut se prononcer contre eux. Cela est particulièrement vrai pour l'index de la table article . À combien de lignes le SGBD s'attend-il à accéder pour un smartressort_id , c'est-à-dire combien de lignes peuvent figurer dans la clause IN ? Si le SGBD pense que cela pourrait bien représenter environ 10% de tous les ID d'article, il peut déjà décider de plutôt lire le tableau séquentiellement plutôt que de se frayer un chemin dans l'index pour autant de lignes.

Donc pour moi, la solution était la suivante:

1   PRIMARY s   const   PRIMARY PRIMARY 4   const   1   Using index
1   PRIMARY a   index   NULL    source_created  7   NULL    25  
1   PRIMARY str ref PRIMARY,ressort_id,idx1 PRIMARY 4   const   1   Using index
1   PRIMARY atr eq_ref  PRIMARY,article_id,idx2 PRIMARY 8   com.nps.lvz-prod.str.ressort_id,com.nps.lvz-prod.a.id   1   Using index; FirstMatch(a)

Cela ne nécessite que ~ 35 ms. Explain ressemble à ceci:

explain SELECT a.*, NOW()
FROM article as a  USE INDEX (source_created)
where a.id in (SELECT atr.article_id
    FROM smartressort AS s
    JOIN smartressort_to_ressort AS str
    ON s.id = str.smartressort_id
    JOIN article_to_ressort AS atr
    ON str.ressort_id = atr.ressort_id
    WHERE s.id = 1
) 
ORDER BY a.created_at DESC
LIMIT 25;

Malgré tout, cette requête Explain me convient mieux, mais je ne sais pas pourquoi exactement:

1   PRIMARY a   index   NULL    source_created  7   NULL    1   
1   PRIMARY <subquery2> eq_ref  distinct_key    distinct_key    4   func    1
2   MATERIALIZED    str ref PRIMARY,ressort_id,idx1 PRIMARY 4   const   1   Using index
2   MATERIALIZED    atr ref PRIMARY,article_id,idx2 PRIMARY 4   com.nps.lvz-prod.str.ressort_id 1262    Using index