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
12303
Date d'inscription
mardi 10 février 2004
Statut
Modérateur
Dernière intervention
30 juillet 2012
40
je viens de te dire que tu ne te trompais PAS...
Messages postés
79
Date d'inscription
dimanche 9 février 2003
Statut
Membre
Dernière intervention
18 juin 2007

Bon après quelques tests voila ce qui ressort sur une table contenant 5000 lignes quand je veux récupérer 1000 lignes.
C'est un peu porcif comme test mais c'est le but

En utilisant LIMIT de mySQL : 0,077 sec
En utilisant un faux limit en PHP avec les itérateurs : 0,0973 sec

Ma question est la suivante : est-ce que c'est grave docteur ?

En augmentant la charge du serveur et la taille de ma table est-ce qu'à long terme ca risque pas de poser de léger problème de rapidité ?

PS: Je sais que j'ai mis la réponse en acceptée... mais j'ai encore besoin de renseignement
Messages postés
12303
Date d'inscription
mardi 10 février 2004
Statut
Modérateur
Dernière intervention
30 juillet 2012
40
Salut

sisi ca va poser un probleme, les algos de tris sont de complexites n*log(n), ensuite faut voir le reste de ta requette, mais ca sera toujours trop lent....

une recherche sur exalead vous aurait peut-etre evite de poser cette question

In a dream, I saw me, drop dead...
U were there, U cried...
It was just a dream,
if I die, U won't cry, maybe, U'll be happy
Messages postés
10840
Date d'inscription
lundi 24 février 2003
Statut
Modérateur
Dernière intervention
2 mars 2010
23
Sérieux...enfin bon...j'utilise un itérateur (mon package oLimit) pour un applicatif  extranet  logistique.  C'est utilisé par de grosses boîtes, y a de gros volumes, ça bouge beaucoup, c'est du sérieux...bref, c'est pas un site web de base, c'est un applicatif web réellement utilisé, sur lequel est basé tout l'aspect logistique de ma boîte, mais aussi celui de pas mal de grosses boîtes.
Et je n'ai aucun soucis de pagination...
Faut pas non plus déconner : la pagination, c'est pour le confort, y a rien de vital. On a 25000 lignes à voir? On pagine par 25, et basta. C'est instantannée. Et ce, même si on fout des tris et des filtres à tout va, et même si on a des jointures sur 5 tables contenant chacune des dizaines de millier de lignes.
Faut pas abuser...le jour où ton système se sentira à l'étroit, c'est soit qu'il sera particulièrement mal foutu, soit tu que tu seras le roi du monde...
Faut arrêter de se prendre la tête pour 1 ou 2 millièmes de secondes quand on ne gère pas la base client de Microsoft. L'essentiel est plutôt d'avoir un joli code, clair et générique.
Messages postés
79
Date d'inscription
dimanche 9 février 2003
Statut
Membre
Dernière intervention
18 juin 2007

Merkiii, je n'en demandais pas tant

J'ai aucune idée des montées en charge et de ce que ca peut donner, je vais bientot arrivé en stage sur le développement d'un gros portail web qui devrait (on l'espère) accueillir bcp bcp bcp de visiteurs, donc vala je m'inquiétais un peu c'est tout
Messages postés
12303
Date d'inscription
mardi 10 février 2004
Statut
Modérateur
Dernière intervention
30 juillet 2012
40
Salut
malalam, tu me loues ton serveur combien ?

une minute pour cette requette :

SELECT
    clients.id, clients.nom, clients.prenom,
    clients.type AS typeclient,            /*Homme Femme, Enfant*/
    clients.civilite,                /*Madamme Mademoiselle Monsieur*/
    clients.date_de_naissance,
    clients.tranche_age,
    clients.adresse,
    clients.tel,
    clients.actif,                    /*oui non */
    ville.nom AS nomville,
    ville.code_postal,
    collaborateur.nom AS collaborateur_nom,
    collaborateur.prenom AS collaborateur_prenom,
    collaborateur.pseudo AS collaborateur_pseudo,
    (
        SELECT COUNT(*)
        FROM passage_liste
        WHERE passage_liste.id_client=clients.id
    ) AS nbr_passages,
    (
        SELECT prestations.nom
        FROM passage_liste LEFT JOIN
            (passage_prestation LEFT JOIN prestations
                ON prestations.id=passage_prestation.id_prestation)
            ON passage_prestation.id_passage=passage_liste.id
        WHERE passage_liste.id_client=clients.id
        GROUP BY passage_prestation.id_prestation
        ORDER BY COUNT(*)
        LIMIT 1
    ) AS prestation_best
FROM
    (clients LEFT JOIN ville ON clients.id_ville=ville.id)
        LEFT JOIN collaborateur ON id_collaborateur_prefere=collaborateur.id

1300 clients, beaucoup d'encaissements...

une recherche sur exalead vous aurait peut-etre evite de poser cette question

In a dream, I saw me, drop dead...
U were there, U cried...
It was just a dream,
if I die, U won't cry, maybe, U'll be happy
Messages postés
10840
Date d'inscription
lundi 24 février 2003
Statut
Modérateur
Dernière intervention
2 mars 2010
23
J'ai pas dit que je n'avais pas un très bon serveur de db... ;-) On le paye assez cher comme ça.
mais quand même! Ce qui prend une minute là, c'est quoi ? L'exécution de la requête (query ()) ou l'affichage qui suit la requête ?
Parce que je suis assez surpris : des requêtes de ce genre j'en ai bcp, avec bcp plus d'enregistrements, et ce n'est jamais aussi lent. Ne me loue jamais ton serveur ... ;-)
Messages postés
12303
Date d'inscription
mardi 10 février 2004
Statut
Modérateur
Dernière intervention
30 juillet 2012
40
Salut
ce qui prend le temps c'est la requette... (executee en console)

t'as meme quand une requette qui a deux left join, avec deux sous requette, l'une d'entre elle a deux left join...

une recherche sur exalead vous aurait peut-etre evite de poser cette question

In a dream, I saw me, drop dead...
U were there, U cried...
It was just a dream,
if I die, U won't cry, maybe, U'll be happy
Messages postés
1406
Date d'inscription
mercredi 17 août 2005
Statut
Membre
Dernière intervention
28 août 2007
8
Salut les gens,

Ben oui, cette requête n'a poutant pas l'air si méchante. Etonnant...

Coucou, si tu étais un débutant posant la question "pourquoi elle prend une minute ma requête?" je t'aurais répondu : "Il manque un ou des indexes sur tes tables, revérfies-les".

Mais ils doivent y être, t'es pas un nouveau... Peut-être que dans ton cas, tu devrais essayer les tables temporaires pour précalculer les sous-requêtes et éviter trop d'ouverture de table :

create temporary table t1 (pk...) engine= memory
    SELECT clients.id, COUNT(*) as nb
        FROM passage_liste, clients
        WHERE passage_liste.id_client=clients.id ;

create temporary table t2 (pk...) engine=memory
    SELECT DISTINCT clients.id, prestations.nom
        FROM passage_liste, passage_prestation, prestations, clients
        WHERE passage_prestation.id_passage =passage_liste.id AND prestations.id= passage_prestation.id_prestation AND passage_liste.id_client=clients.id ;

SELECT
    clients.id, clients.nom, clients.prenom,
    clients.type AS typeclient,            /*Homme Femme, Enfant*/
    clients.civilite,                /*Madamme Mademoiselle Monsieur*/
    clients.date_de_naissance,
    clients.tranche_age,
    clients.adresse,
    clients.tel,
    clients.actif,                    /*oui non */
    ville.nom AS nomville,
    ville.code_postal,
    collaborateur.nom AS collaborateur_nom,
    collaborateur.prenom AS collaborateur_prenom,
    collaborateur.pseudo AS collaborateur_pseudo,
    t1.nb AS nbr_passages,
    t2.nom AS prestation_best
FROM
    (clients LEFT JOIN ville ON clients.id_ville=ville.id)
        LEFT JOIN collaborateur ON id_collaborateur_prefere=collaborateur.id
    LEFT JOIN t1 on ( clients.id = t1.id)
    LEFT JOIN t2 on ( clients.id = t2.id)

Justement, revenant sur ma méthode : je me suis rendu-compte qu'elle ne pouvait en fait jamais être gagnante... Et pour la même raison : ouvertures de table ! Ce qui est long en MySQL c'est d'ouvrir une table pour la parcourir (et non d'en sortir un gros volume de données comme disait malalam). Or dans tous les cas, ma méthode ammènera à ouvrir une nouvelle table : la table temporaire créée pour l'occasion. Donc ça ne peut pas faire !

Bref, A+
Messages postés
10840
Date d'inscription
lundi 24 février 2003
Statut
Modérateur
Dernière intervention
2 mars 2010
23
Ouais, bizarre...
Celle-là :
SELECT
c.cmd_id,
c.op_id,
CONVERT(VARCHAR, c.cmd_date, 103) AS cmd_date,
c.cmd_sent,
c.dest_id,
c.cmd_poids_theo,
c.cmd_poids_reel,
CONVERT(VARCHAR, c.cmd_date_liv, 103) AS cmd_date_liv,
c.cmd_urg,
COALESCE(NULLIF(c.cmd_colis, ''), '0') AS cmd_colis,
COALESCE(NULLIF(c.cmd_palettes, ''), '0') AS cmd_palettes,
COALESCE(NULLIF(c.tp_id, ''), '0') AS tp_id,
c.cmd_ref,
c.cmd_extref,
c.cmd_comment,
c.cmd_raison,
d.dest_ref,
c.cmd_cp,
c.cmd_ville,
c.cmd_pays,
o.op_libelle,
comt.comt_libelle,
ent.ent_libelle,
comt.comt_id,
ent.ent_id,
(SELECT count(*) FROM cmd_articles cmda WHERE cmda.cmd_id = c.cmd_id) AS nb_lignes,
(SELECT sum(art_quantite) FROM cmd_articles cmda WHERE cmda.cmd_id = c.cmd_id) AS total_articles
FROM commandes c
LEFT JOIN destinataires d ON d.dest_id = c.dest_id
LEFT JOIN commettants comt ON comt.comt_id = d.comt_id
LEFT JOIN entites ent ON ent.ent_id = d.ent_id
LEFT JOIN vag_commandes vc ON vc.cmd_id = c.cmd_id
LEFT JOIN operations o ON o.op_id = c.op_id

prend 17 secondes dans l'analyseur de requêtes de mssql server (avec affichage, donc).
Et j'ai ajouté les 2 dernières sous requêtes parce que sinon c'était moins significatif. Et elle ramène 21000 lignes; et les tables qu'elle ouvre ont pour la plupart plusieurs 10aines de milliers de lignes.
Donc je suis vraiment surpris.