Bonjour,

J'ai une requête SQL que je voudrais optimiser

SELECT * FROM forum_messages WHERE id_topic = 1 ORDER BY date ASC LIMIT 0,25

Qu'est ce qui est le mieux au niveau des index pour ça ?

1 : Mettre id_topic en index
2 : Mettre id_topic en index, et date en index, les 2 étant distincts
3 : Mettre id_topic ET date en index commun

Merci.

Je pense que ça va dépendre de ce que tu veux faire avec tes topics, mais en général c'est toujours mieux de les distinguer pour pouvoir faire des recherches sur les dates ensuite !

Rien ne m'empêche de faire les deux

- Un index double id_topic et date
- 2 index simple, 1 id_topic et 1 date

Ca rajoute juste du poids dans la table, ca me gêne pas trop, c'est plus la lenteur des requêtes que je veux améliorer.

Mais un ORDER BY utilise ou pas les index ? Oui du coup vu ta réponse ?

Oui c'est possible !

Déjà, id_topic est à priori soit une clé primaire, soit une clé étrangère. Donc, forcément un index, ou alors c'est que le schéma de la table est déjà mal fait.

Ensuite, effectivement, la date peut être mise en index, il s'en servira certainement. Le meilleur moyen de le voir est un EXPLAIN ou de faire des tests pour les deux, le seul problème des tests c'est que la durée de la requête dépend beaucoup trop du travail que le SGBD est en train d'effectuer et de son environnement à un moment x.

Après, il faut voir combien de réponses tu es censé à voir. Sur une moyenne de 10 réponses par topic, l'index peut devenir intéressant sur la date, mais pas obligatoire (trier 10 résultats, on peut pas dire que c'est vraiment difficile). Par contre, la clé étrangère (à priori s'en est une), pour les contraintes relationnelles, nécessite un index. Donc, elle est déjà indexée, et elle le doit car si tu as 5000000 Messages, il est bien de pouvoir rapidement en tirer ceux qui sont pour le topic x. La contrainte relationnelle avec les pseudo triggers CASCADE, te permettront d'avoir tes messages effacés lorsque tu effaces ton topic (d'autres comportements sont possibles). Ce qui garantit l'intégrité de ta base de données et une seule requête au lieu de 2, avec l'obligation d'une transaction pour s'assurer de l'intégrité.

a+

Dans ma table forum_message id_topic est bien une clé étrangère faisait référence au champs id de ma table forum_topic. Mais nul par dans myslq je l'ai renseigné (je savais pas que c'était possible), c'est pour ça que j'ai mis actuellement id_topic en INDEX avec aussi un INDEX sur le champs date.
Je dispose de topic très lourds (centaines voir milliers de messages par topic parfois).

Merci déjà pour la confirmation de l'utilisation de l'index sur le ORDER BY.

Après je me demandais si en théorie c'était intéressant de mettre un INDEX comprenant 2 champs, sur les champs id_topic + date (en plus du champs index sur id_topic et du champs index sur date qui eux sont utilisés pour d'autres requêtes par exemple) pour ma requête donnée dans mon premier post en particulier (car c'est elle qui est lente parfois), vu qu'elle utilise ces deux champs la.

Ca doit bien servir à quelque chose de pouvoir faire des index sur plusieurs champs à la fois plutot que de faire autant d'index que je champs.

Dans ma table forum_message id_topic est bien une clé étrangère faisait référence au champs id de ma table forum_topic. Mais nul par dans myslq je l'ai renseigné (je savais pas que c'était possible), c'est pour ça que j'ai mis actuellement id_topic en INDEX avec aussi un INDEX sur le champs date.
Je dispose de topic très lourds (centaines voir milliers de messages par topic parfois).

Merci déjà pour la confirmation de l'utilisation de l'index sur le ORDER BY.

Après je me demandais si en théorie c'était intéressant de mettre un INDEX comprenant 2 champs, sur les champs id_topic + date (en plus du champs index sur id_topic et du champs index sur date qui eux sont utilisés pour d'autres requêtes par exemple) pour ma requête donnée dans mon premier post en particulier (car c'est elle qui est lente parfois), vu qu'elle utilise ces deux champs la.

Ca doit bien servir à quelque chose de pouvoir faire des index sur plusieurs champs à la fois plutot que de faire autant d'index que je champs.

Et pourquoi tu fais pas des tests ? En local c'est relativement stable (et sur un distant tu peux faire plusieurs essais successifs).
A mon avis concernant ta requête initiale, ce n'est pas la peine de faire un index commun sur les deux champs surtout qu'ils sont déjà indexés séparément. Au contraire cela devrait ralentir ta requête car le chargement des index prend du temps.

Concernant les index et order by c'est ici

Mais bon pour en avoir le coeur net :

$time = microtime(true);
//exécution requête
$time_end = microtime(true);
$time_tot = $time_end - $time;
echo 'durée exécution  = ' . $time_tot.'<br />';

Puisque ta table est importante tu devrais assez vite voir des différences (en prenant soin toutefois d'utiliser la même variable dans la clause WHERE pour comparer l'efficacité des différentes indexations).

J'ai pas en local (trop lourd plusieurs go de sql, trop compliqué car j'ai pas de linux pour le faire tourner et le serv ou il est a plein de truc installés par le mec qui gère les serv comme cache php, gzip et d'autres truc d'optimisation) et en distant la charge est jamais pareil :/ Pis la second fois la requête est cachée.

"cela devrait ralentir ta requête car le chargement des index prend du temps"

Merci de l'info je savais pas.

Je vais nettoyer les index doubles alors si des simples suffises (mais faudrait que je trouve quand même l'interêt d'index doubles);

Un index double serait utile si tu n'avais pas déjà indexé individuellement les deux colonnes.

Il y a un peu de doc ici

Bonjour,

...
SELECT * FROM forum_messages WHERE id_topic = 1 ORDER BY date ASC LIMIT 0,25
...

J'ai un doute sur le fait qu'un sgbd soit capable d'utiliser 2 index sur une table pour une requête.

Pour moi

SELECT * FROM forum_messages WHERE id_topic = 1 ORDER BY id_topic, date ASC LIMIT 0,25

avec un index sur id_topic, date.
Normalement, l'index (d_topic, date) sera utilisé pour le where et pour le order by

Bonjour,

...
SELECT * FROM forum_messages WHERE id_topic = 1 ORDER BY date ASC LIMIT 0,25
...

J'ai un doute sur le fait qu'un sgbd soit capable d'utiliser 2 index sur une table pour une requête.

Pour moi

SELECT * FROM forum_messages WHERE id_topic = 1 ORDER BY id_topic, date ASC LIMIT 0,25

avec un index sur id_topic, date.
Normalement, l'index (d_topic, date) sera utilisé pour le where et pour le order by

Arf tu as raison, j'ai dit des bêtises Puisque id_topic n'est pas unique, l'index double sera utilisé avec profit.

Bonjour,

...
SELECT * FROM forum_messages WHERE id_topic = 1 ORDER BY date ASC LIMIT 0,25
...

J'ai un doute sur le fait qu'un sgbd soit capable d'utiliser 2 index sur une table pour une requête.

Pour moi

SELECT * FROM forum_messages WHERE id_topic = 1 ORDER BY id_topic, date ASC LIMIT 0,25

avec un index sur id_topic, date.
Normalement, l'index (d_topic, date) sera utilisé pour le where et pour le order by

C'est pas une perte de temps le ORDER BY id_topic ?
Vu que dans le WHERE je spécifie un seul topic y'a pas de raison de faire un tri ?

Ou alors si on ne le met pas, l'index double (id_topic,date) ne sera pas utilisé ?
Je pensais que si vu que la requête utilise le id_topic dans le WHERE et le date dans le ORDER BY

Evidemment si tu n'as pas besoin du ORDER BY dans ta requête, seul l'index sur le champ de la clause WHERE sera utilisé et ton index double ne servira pas pour cette requête.

mmm d'accord, je crois que je viens de comprendre.

L'index double (id_topic,date) est utile que si j'ai

WHERE id_topic = .. AND date = ..
ou
ORDER BY id_topic, date

Mais pas utilisé si j'ai WHERE id_topic = .. ORDER BY date

Il faut que les 2 champs de l'index soit au même endroit et pas l'un dans une condition et l'autre dans un tri.
Donc dans mon cas, pas d'utilité d'index double pour ma requête.

Nan...
L'index double id_topic_date sera utilisé - même si par ailleurs les champs id_topic et date son indexés individuellement - si tu écris :

WHERE id_topic = .. ORDER BY date

Par contre - toujours si les champs id_topic et date son indexés individuellement - seul l'index sur le champ WHERE sera utilisé si tu écris :

WHERE id_topic = ... (sans avoir postérieurement de clause ORDER BY)

et seul l'index sur le champ ORDER BY sera utilisé si tu écris :

ORDER BY date ... (sans avoir préalablement de clause WHERE)

(Evidemment cela suppose que id_topic n'est pas unique dans ta table sinon le ORDER BY ne sert à rien)