Sélection de lignes d'une base sans utiliser LIMIT

[equids]

En lisant quelques articles sur internet, j'ai cru comprendre que la fonction LIMIT était assez consommatrice de ressources,

en effet, même avec LIMIT, le serveur doit parcourir toutes les lignes de la table pour donner le résultat désiré.

Je voudrais savoir si il y aurait une alternative à cette fonction pour 2 cas :

- Sélection des X derniers lignes d'une table SQL

- Sélction de X éléments à partir du rang Y d'une table SQL

Merci

[Kioob]

Hello,

un peu de lecture à ce sujet, ou du moins sur la pagination de manière générale : http://www.mysqlperformanceblog.com/2008/0...nated-displays/

[f_trt]

Il faut les bons index voila tout tu as aussi la commande explain.

[Kioob]

Index ou pas, quand il y a 50 millions d'enregistrements dans la table, un "LIMIT" a tendance à pédaler sec.

[equids]

Hello,

un peu de lecture à ce sujet, ou du moins sur la pagination de manière générale : http://www.mysqlperformanceblog.com/2008/0...nated-displays/

Merci pour le lien, mais j'ai un peu de mal avec le langage technique en Anglais,

je ne suis pas arrivé a voir si il propose une technique alternative ou simplement des pistes.

- Ne pas afficher toutes les pages, mais seulement un "suivant" et "précédent"

- Limiter sa recherche a un nombre fixé de lignes max

- Mettre certains résultats en cache (mais je n'ai pas vraiment compris comment ça fonctionne)

...

Bref, je n'ai pas vu une autre manière d'ecrire sa requête SQL.

Tu dis à juste titre qu'un "LIMIT" sur des millions d'enregistrements, pompe trop de ressources.

Dans ca cas, comment arriver au même résultat sans utiliser ce "LIMIT" ?

J'ai lu une proposition avec un BETWEEN. Mais, si il y a des enregistrements effacés, le résultat c'est plus très exact vu qu'il sélectionne des lignes entre tel et tel numéro sans se soucier de savoir si cet intervalle est complet ou pas.

[Kioob]

Il n'y a pas vraiment d'autre manière d'écrire le "LIMIT" : c'est l'approche qui n'est pas optimale.

Pour se faire une idée, prend un livre sur une étagère et fait la liste des 20 premiers mots après les 2743 premiers.

Pour pouvoir les lister tu vas être obligé de lire un grand nombre de "pages", et de compter les mots de chacune d'elle... cela va te prendre beaucoup de temps.

Ce qu'il faut donc c'est un moyen de très fortement réduire l'espace de recherche. Par exemple si je te dis que la page n°240 de ton livre commence par le mot 2509, tu vas gagner beaucoup de temps non ?

Le problème est donc de trouver une autre approche pour arriver à ce résultat. Mais ça, ça dépend essentiellement de tes données, et ce n'est pas toujours possible.

C'est pour ça qu'à moins d'avoir un volume de données conséquent, on se contente bien souvent d'une approche via LIMIT qui bien que "pas super propre" est très simple à mettre en place.

Pour le coup je me rend d'ailleurs compte que le lien que je t'ai indiqué ne répond même pas à cette problématique, mais à celle du "nombre de pages". Dans toutes les solutions proposées dans cet article le "LIMIT" est toujours utilisé. Désolé pour la confusion.

Bref, prenons un de tes cas : "Sélection des X derniers lignes d'une table SQL" et admettons qu'il s'agit d'une table "news" dont tu veux extraire les 5 dernières entrées. Cette table contient 3 ans d'historique et quelques 800 enregistrements. Soit en moyenne 22 entrées par mois. Le champ "dateNews" est évidement indexé.

Avec un "order by dateNews desc limit 5", MySQL va être amené à travailler sur la totalité des 800 enregistrements (1) ; puis n'en conservera que 5.

Pour l'aider on peut indiquer une clause de type "where" et indiquer de ne chercher que parmi les enregistrements des 30 derniers jours ("where dateNews > curdate() - interval 30 day order by dateNews desc limit 5"), et MySQL ne travaillera cette fois que sur la vingtaine d'enregistrements concernés, soit 40 fois moins de données à traiter (jointures, tris, etc). L'inconvénient c'est qu'on suppose ici qu'il y aura toujours au moins 5 news par période de 30 jours, sinon cela ne fonctionnera pas...

C'est une des approches possibles, il y en a évidement d'autres, mais chacune est spécifique aux données qu'il y a derrière.

(1) ce n'est pas forcément vrai pour un cas aussi simple, mais peux très vite varier selon le tri, les clauses et les jointures.

[equids]

Merci de ta réponse claire

Ok, donc le but du jeu, c'est de restreindre au maximum le segment sur lequel la recherche va se faire.

1ère question : Lorsqu'on utilise une clause "WHERE", la base n'a pas a parcourir les éléments qui n'en font pas partie ?

Elle arrive directement à les sélectionner sans "lire" toutes les lignes ?

Et la 2ème, c'est plutot un cas classique pour avoir ton avis :

Je veux classer et afficher les différentes pages d'un post d'un forum dont les lignes sont enregistrées dans une base qui contient tout ce forum (toutes les lignes quoi).

Comment faire pour restreindre au max les lignes parmis lesquelles on lancera notre recherche ?

[Kioob]

Pour la première question oui, dès lors qu'il y a des indexes qui correspondent à la clause en question. Un "explain" de la requête indique le nombre d'enregistrements que MySQL parcours dans chacune des tables ; c'est assez pratique pour vérifier l'utilisation (ou non) des indexes.

Pour ce qui est du cas du forum, il faudrait regarder comment procèdent des forums "biens faits" tels que Phorum (ou phpBBv3 je suppose, bien que je n'ai jamais regardé le code).

Pour ma part, je ne suis pas certain que mettre en place une solution différente de LIMIT soit nécessaire dans ce cas : avoir plusieurs milliers de messages par sujet me semble assez rare non ?

A l'époque où je maintenais mon propre forum, la requête qui posait problème était plutôt celle qui listait les sujets d'une catégorie. Là le volume peut être très conséquent, et c'est un affichage très fréquent avec peu de mise en cache possible... il faut donc trouver une solution adaptée.

Pour ce cas particulier je pense que j'essayerais de maintenir un champ "position" (indexé), en prenant garde qu'il soit le moins souvent possible mis à jour, et que les éventuels problèmes de doublon ne soient pas bloquants.

Ainsi il suffirait de deux requêtes "simples" : (j'ai remplacé l'arobase par un dollars, vu que le forum colle des _AT_ partout )

- select $pos := max(position) from sujet;

- select XXXX from sujet where position between $pos - 20 and $pos order by position desc;

Reste à maintenir efficacement ce champ "position".

Là encore, ce n'est qu'une approche possible "à chaud". Je pense que les gros forums se sont penché sur la question depuis longtemps.

[Dudu]

Salut, et désolé de m'incruster dans cette discussion, mais je la suis avec beaucoup d'intérêt.

Et j'ai une question qui me vient lorsque je lis l'échange suivant:

Le champ "dateNews" est évidement indexé.

[...]

Pour l'aider on peut indiquer une clause de type "where" et indiquer de ne chercher que parmi les enregistrements des 30 derniers jours ("where dateNews > curdate() - interval 30 day order by dateNews desc limit 5"), et MySQL ne travaillera cette fois que sur la vingtaine d'enregistrements concernés, soit 40 fois moins de données à traiter (jointures, tris, etc).

Ok, donc le but du jeu, c'est de restreindre au maximum le segment sur lequel la recherche va se faire.

1ère question : Lorsqu'on utilise une clause "WHERE", la base n'a pas a parcourir les éléments qui n'en font pas partie ?

Elle arrive directement à les sélectionner sans "lire" toutes les lignes ?

Pour la première question oui, dès lors qu'il y a des indexes qui correspondent à la clause en question. Un "explain" de la requête indique le nombre d'enregistrements que MySQL parcours dans chacune des tables ; c'est assez pratique pour vérifier l'utilisation (ou non) des indexes.

J'ai un champ "id" en auto-increment qui compte mes enregistrements dans la table. Quasiment tous les champs sont indéxés.

Cela voudrait dire que je consomme moins de ressources en faisant un

WHERE id <= 50

qu'en faisant un

LIMIT 0, 50

???

(je parle pour une base qui est assez grosse et qui grossit de manière exponentielle).

PS: Désolé pour les @ c'est un peu la seule solution pour éviter que trop d'adresses mail soient en clair sur les forums.

Ça évite un tant soit peu la récupération par des robots collecteurs.

[equids]

- select _AT_pos := max(position) from sujet;

Cette requete sert a quoi au juste ?

Je vais peut être me pencher sur le codes des forums "tous prêts" pour avoir des idées d'optimisation.

[Kioob]

Dudu : en supposant qu'il y ait aussi un "ORDER", alors il y a de grandes chances oui, à vérifier avec un EXPLAIN.

Mais attention ça ne fait pas du tout la même chose : les ID auto increment ne se suivent pas forcément, surtout en cas de suppression. Si tu effaces les ID 40,41,42,43 et 44, la première requête ne te retournera plus que 45 enregistrements.

Quasiment tous les champs sont indéxés.

Tu es sûr de ton coup ? Les indexes améliorent généralement les perfs des SELECT, mais ralentissent fortement les INSERT et UPDATE.

PS: Désolé pour les @ c'est un peu la seule solution pour éviter que trop d'adresses mail soient en clair sur les forums.

Ça évite un tant soit peu la récupération par des robots collecteurs.

Je me doute bien c'est juste dommage que ce soit également remplacé entre les balises [ code ].

Cette requete sert a quoi au juste ?

Je vais peut être me pencher sur le codes des forums "tous prêts" pour avoir des idées d'optimisation.

Elle récupère la valeur "position" la plus grande... et la stocke dans une variable MySQL. Mais tu peux parfaitement utiliser une variable PHP à la place.

[Dudu]

Dudu : en supposant qu'il y ait aussi un "ORDER", alors il y a de grandes chances oui, à vérifier avec un EXPLAIN.

Bon à savoir, alors Oui il y a un ORDER, je me ferais un petit benchmark perso avec EXPLAIN

Mais attention ça ne fait pas du tout la même chose : les ID auto increment ne se suivent pas forcément, surtout en cas de suppression. Si tu effaces les ID 40,41,42,43 et 44, la première requête ne te retournera plus que 45 enregistrements.

Oui, pour ça pas de risque. Mais je note quand même

Tu es sûr de ton coup ? Les indexes améliorent généralement les perfs des SELECT, mais ralentissent fortement les INSERT et UPDATE.

Après vérif, je confirme: tous les champs sont indexés, sauf bien sur le "id" qui est en UNIQUE.

Je me doute bien c'est juste dommage que ce soit également remplacé entre les balises [ code ]

Seul certains BBCodes ne sont pas interprétés dans les balises CODE et CODEBOX (quoique les deux ont un comportement un peu différent l'une de l'autre).

Dans le cas du @ rien à voir avec le BBCode donc c'est un peu différent. Il s'agit de la fonction de remplacement de mot d'Invision, et elle fonctionne partout.

En revanche, les modos et admins peuvent écrire de vrais @ donc comme tu le vois, je te n@rgue