Pagination optimisée [Résolu]

Signaler
Messages postés
79
Date d'inscription
dimanche 9 février 2003
Statut
Membre
Dernière intervention
18 juin 2007
-
Messages postés
10840
Date d'inscription
lundi 24 février 2003
Statut
Modérateur
Dernière intervention
2 mars 2010
-
Salut tout le monde,

Je suis actuellement en train de faire une classe SQL qui gererait plutôt pas mal de truc dont j'ai besoin dont la pagination !

J'hésite entre plusieurs solutions et je voulais savoir laquelle était préférable, laquelle à éviter ou si vous aviez d'autres solutions

Voila donc mes solutions:


<li>
Récupérer la totalité de la table (toutes les lignes) pour savoir combien il y a de lignes et ensuite faire ajouter un LIMIT dessus et refaire la meme requete (je pense que c'est la pire idée parce que je fais 2 requêtes et la première mange bcp de ressources si la table est importante) -> 2 requetes (tres proche l'une de l'autre)

</li>
<li>
Récupérer la totalité de la table pour savoir combien il y a de lignes et simuler un limit en php grâce aux itérateurs (pas trop mal mais la première requête mange bcp quand meme et je me demande si il ne vaut pas mieux laisser au SQL ce qui lui appartient, le limit notamment) -> 1 requete

</li>
<li>
Faire un count sur toute la table et ensuite executer ma requête avec le LIMIT. -> 2 requetes

</li>

Finalement je pense que c'est la dernière solution qui est préférable mais comme c'est le genre de truc qui va etre repeté beaucoup de fois (plus d'une 20aine de fois) sur le site sur lequelle je bosse et a chaque fois pour des tables différentes, donc la première solution (qui est la solution actuelle mais qui me plait pas du tout) me permet de rendre un peu plus générique ma pagination alors que la solution 3 va m'obliger à écrire à chaque fois mes 2 requêtes.

Voili voulou, merci de pataper et merci d'avance

Knolan

30 réponses

Messages postés
10840
Date d'inscription
lundi 24 février 2003
Statut
Modérateur
Dernière intervention
2 mars 2010
23
hello,
La 2ème solution...c'est la plus générique, et de loin la meilleure.
LIMIT n'existe pas sur tous les serveurs de BDD, c'est propriétaire.
C'est aussi très optimisé : ce qui prend bcp de temps c'est de rapatrier les résultats d'une requête, rarement la requête en elle-même.
Messages postés
10840
Date d'inscription
lundi 24 février 2003
Statut
Modérateur
Dernière intervention
2 mars 2010
23
Coucou => Bah, je récupère plusieurs dizaines de millier de lignes sur de grosses tables avec jointures tous les jours, et réellement, les requêtes sont très rapides (sous mssql)
Je limite mes résultats avec ma classe oLimit, et ça fonctionne à merveille, c'est instantannée. Maintenant le between c'est très bien, mais difficile, souvent, d'avoir des suites d'ID sans trou : on ne cherche pas tjrs des ID se suivant : je veux tous les gens dont le nom commence par 'C', et je veux paginer. Par exemple. Tu n'auras pas des ID se suivant, forcément.
Messages postés
12303
Date d'inscription
mardi 10 février 2004
Statut
Modérateur
Dernière intervention
30 juillet 2012
39
Salut,

le plus optimise c'est d'avoir un id sans trous pour pouvoir faire du WHERE id BETWEEN a AND b...
Messages postés
12303
Date d'inscription
mardi 10 février 2004
Statut
Modérateur
Dernière intervention
30 juillet 2012
39
"rarement la requête en elle-même."
=> on ne doit pas faire les memes requettes
Messages postés
79
Date d'inscription
dimanche 9 février 2003
Statut
Membre
Dernière intervention
18 juin 2007

D'accord et niveau perf (si on oublie l'abstraction BDD que je ne vais sans doute pas implémenter et comme je vais rester sur MySQL) ca donne quoi de faire le limit avec les itérateurs plutot qu'avec un LIMIT ?
Messages postés
12303
Date d'inscription
mardi 10 février 2004
Statut
Modérateur
Dernière intervention
30 juillet 2012
39
Salut, sur mon avant derniere source t'as des requettes vraiment lentes... j'en ai qui passent la minute sur ce projet
Messages postés
1406
Date d'inscription
mercredi 17 août 2005
Statut
Membre
Dernière intervention
28 août 2007
7
Salut,

Concernant ce point, j'ai lu dans le manuel mysql qu'une bonne methode serait :

1. Tu déclares une variable SQL de position

mysql_query('set @pos=0');


2. Tu créés une requête avec tes critères mais ne sortant que l'index de la table ET les champs que tu ne veux calculer qu'une fois (mais pas les autres pour éviter de blinder la mémoire!) ET la position selon l'ordre (ou sans ordre d'ailleur)
$select = "select @pos:=@pos+1 as pos, id, match(champ_texte) against('recherche') as score from table where champ_texte like '%$recherche%' order by score desc";

3. Tu balances ta requête dans une table temporaire en memoire avec index sur la position :
$create_table =  "create temporary table t (pos int unsigned not null, primary key (pos)) engine=memory ";
mysql_query($create_table.$select);

4. Le nombre de lignes trouvées par la recherche sort de suite via :
$nb_lignes = mysql_affected_rows(); // nombre de lignes insérées

5. Ensuite, tu chopes les identifiants de ta page en liant le reste des données via la table "mère" :
$nb_par_page = 10;
$start_pos = 30; // fais ta regle de calcul des pages avec $nb_lignes ici
$end_pos = $start_pos + $nb_par_page;

$select = "select t.id, t.score, m.champ_texte, m.autres_champs from t,table_mere as m where pos between $start_pos and $end_pos"; // optimal car la position est l'indexe !!!
$result = mysql_query($select);

Perso, je n'ai jamais vraiment utiliser ce système.
Qu'en pensez vous... (malalam et coucou)?

Niveau perf, je sais que la création d'une table temporaire est un peu coûteuse en temps. Mais ce temps est censé être indépendant de la complexité de la recherche et du nombre de lignes...

A+
Messages postés
1406
Date d'inscription
mercredi 17 août 2005
Statut
Membre
Dernière intervention
28 août 2007
7
Petite relecture et errata :

FAUX  : $create_table =  "create temporary table t (pos int unsigned not null, primary key (pos)) engine=memory ";
VRAI : $create_table =  "create temporary table t (primary key (pos)) engine=memory ";

Et petite note : On ne peut mettre des champs blob ou texte dans une table en mémoire (question de place...) Si tu est obligé de le faire, ben alors il reste les autres types. Mais là j'ai des doutes niveau perf.

A+
Messages postés
79
Date d'inscription
dimanche 9 février 2003
Statut
Membre
Dernière intervention
18 juin 2007

Une solution technique assez belle semble-t-il mais je me demande tout de même si c'est plus rapide.

En tout cas merci de présenter une telle solution c'est toujours interessant à connaître
Messages postés
1406
Date d'inscription
mercredi 17 août 2005
Statut
Membre
Dernière intervention
28 août 2007
7
Je trouve aussi... Faudrait avoir une grosse base avec des tables bien immense pour tester et j'en ai pas sous la main.

Mais, vu qu'on arrive sur le sujet, je vais faire un test...

Je vous dis dans qque instants si ça vaut vraiment le coup!
Messages postés
12303
Date d'inscription
mardi 10 février 2004
Statut
Modérateur
Dernière intervention
30 juillet 2012
39
Et que va t'il se passer lorse-que  deux personnes vont demander la page en meme temps?
Messages postés
1406
Date d'inscription
mercredi 17 août 2005
Statut
Membre
Dernière intervention
28 août 2007
7
Aucun pb grace aux tables temporaires (doc mysql) :


Depuis la version 3.22 de MySQL, vous pouvez utiliser le mot
réservé
TEMPORARY
lorsque vous créez une
table. Une table temporaire sera immédiatement effacée dès
que la connexion se termine. Cela signifie que vous pouvez
utiliser le même nom de table temporaire depuis deux connexions
différentes sans risque de conflit entre les connexions. Vous
pouvez aussi utiliser une table temporaire qui a le même nom
qu'une table existante (la table existante est alors cachée
tant que dure la table temporaire). En MySQL version 4.0.2 ou
plus récent, vous avez juste à avoir le privilège
CREATE TEMPORARY TABLES
pour créer des
tables temporaires.
Messages postés
12303
Date d'inscription
mardi 10 février 2004
Statut
Modérateur
Dernière intervention
30 juillet 2012
39
ok
Messages postés
1406
Date d'inscription
mercredi 17 août 2005
Statut
Membre
Dernière intervention
28 août 2007
7
Bon, ma methode est plus longue avec une table de 3000 lignes :

Temps total : 2.93835544586 ms
contre Temps total : 0.130335569382 ms pour la technique n°2...

(recherche trouve 516 lignes, paluchées 10 par 10)
Messages postés
12303
Date d'inscription
mardi 10 février 2004
Statut
Modérateur
Dernière intervention
30 juillet 2012
39
Et ma methode ?
Messages postés
1406
Date d'inscription
mercredi 17 août 2005
Statut
Membre
Dernière intervention
28 août 2007
7
Ben, je peux me tromper, mes ta methode ne semble pas pouvoir utiliser un ordonnencement par "score de recherche"...

id between a and b => on sort un les id de 1 à 10 en page 1, puis 11 à 20 en page 2, etc...

Je me trompe ?

Mais, sinon, à coup sûr elle est la plus rapide puisque c'est la seule à ne pas sortir tous les resultats à chaque fois.
Messages postés
79
Date d'inscription
dimanche 9 février 2003
Statut
Membre
Dernière intervention
18 juin 2007

En effet le résultat du test est assez significatif !

Je vais partir pour un limit via php en modifiant mon itérateur je pense.

En tout cas merci pour tout =)
Messages postés
12303
Date d'inscription
mardi 10 février 2004
Statut
Modérateur
Dernière intervention
30 juillet 2012
39
non tu ne te trompes pas, c'est uniquement quand on a un id classe et sans trous
Messages postés
10840
Date d'inscription
lundi 24 février 2003
Statut
Modérateur
Dernière intervention
2 mars 2010
23
En tous cas, ta méthode, J_G est intéressante (je ne l'avais jamais vue).
Je testerai ça. Ceci dit, on a ici un système utilisant bcp les tables temporaires (sur mssql tjrs) et bon...si c'est pratique, ça reste un peu bordélique et coûteux en ressource.
Néanmoins, j'essayerai quand même cette méthode, c'est très intéressant.

Je suis aussi certain que la méthode de Coucou est la plus rapide, mais elle est aussi la moins souple à mon avis (pas d'autres clauses possible que le between si on veut garder une cohérence).
Messages postés
1406
Date d'inscription
mercredi 17 août 2005
Statut
Membre
Dernière intervention
28 août 2007
7
knolan : pas de pb.

coucou747 : je suis étonné, et ne vois pas comment c'est possible... Tu peux me donner un exemple de requête avec un match() against as score et order by score desc STP?
Je l'étudierais demain ou lundi

A+