Forum |  HardWare.fr | News | Articles | PC | Prix | S'identifier | S'inscrire | Aide | Shop Recherche
850 connectés 

  FORUM HardWare.fr
  Programmation
  SQL/NoSQL

  Optimiser une pagination ?

 



 Mot :   Pseudo :  
 
Bas de page
Auteur Sujet :

Optimiser une pagination ?

n°1976831
welcominh
Posté le 24-03-2010 à 14:51:45  profilanswer
 

Bonjour à tous,
 
Je suis sur un problème d'optimisation donc j'ignore s'il existe une solution. J'ai donc 2 questions.
Disons que je gère une bibliothèque. J'ai une table "livre". Je souhaite lister les livres commençant par la lettre B, dans l'ordre alphabétique, à raison d'une pagination de 10 livres par page.
 
La requête est "toute simple":
 

Code :
  1. SELECT
  2.         id_livre,
  3.         nom_livre,
  4.         ...quelques autres champs
  5. FROM
  6.         livre
  7. WHERE
  8.         nom_livre LIKE 'B%'
  9. ORDER BY
  10.         nom_livre ASC
  11. LIMIT
  12.         0, 10


 
 
Mes 2 questions sont:
- Si je ne me trompe pas, le LIKE n'exploite pas les index. Donc toute la table des livres est lue si je ne m'abuse. Comme optimiser ca ? Avec le FULL-TEXT ?
- Comment optimiser une pagination ? Ce point est beaucoup plus flou que le 1er. Sachant qu'il m'arrive de supprimer des livres ou d'en invalider, et qu'il y a par conséquent des "trous", la méthode du BETWEEN ne marche pas. Pour le moment, je fais un habituel LIMIT, ce qui n'empêche pas le parcours total. En l'occurrence, cela lira tous les livres commencant par B, mais si je souhaite afficher une liste générale des livres, ca lira l'entiereté de la table. :/
 
 
Merci pour votre aide.  :jap:


Message édité par welcominh le 24-03-2010 à 15:00:05

---------------
Direct-download.com, le moteur de recherche pour Mega
mood
Publicité
Posté le 24-03-2010 à 14:51:45  profilanswer
 

n°1976963
Oliiii
Posté le 24-03-2010 à 17:25:23  profilanswer
 

Si je me souviens bien LIKE n'utilise pas les index quand il commence par % (ce qui est normal vu qu'il ne sait pas ce qu'il cherche).  
Dans les autres cas il utilise l'index.
 
Pour ta 2eme question je ne connais pas assez MySQL pour te proposer une solution vallable.

n°1976971
rufo
Pas me confondre avec Lycos!
Posté le 24-03-2010 à 17:56:31  profilanswer
 

Moi, j'ai une question bête : ça vaut le coup de te prendre la tête pour ça :??: Perso, j'ai une BD avec plus de 300000 enregistrements et les like + limit pour me tableaux paginés, ça marche très bien, pas de pb de perfs. Alors avant de te faire des nœuds au cerveau, pose toi la question si c'est utile de chercher à optimiser. Si c'est pour gagner 0.001s, la réponse est non :/
Par contre, tu devrais plutôt regarder du côté du tuning de mysql (genre la taille du cache des résultats de requêtes, du cache des tables temporaires, du cache pour le tri...). Là, ça peut te faire gagner beaucoup si tu travailles sur de très grosses tables (plusieurs 100ène de milliers d'enregistrements, en-dessous, ça vaut pas trop le coup en général).


---------------
Astres, outil de help-desk GPL : http://sourceforge.net/projects/astres, ICARE, gestion de conf : http://sourceforge.net/projects/icare, Outil Cantine Calandreta : http://sourceforge.net/projects/canteen-calandreta
n°1977014
welcominh
Posté le 24-03-2010 à 20:30:21  profilanswer
 

Oliiii> Un explain de mysql a confirmé en effet tes dires, le LIKE utilise l'index s'il ne commence pas par un %. Cool, je savais meme pas ! Merci du tuyau.

 

rufo> J'ai par ex un de mes sites qui fait environ 2500 VU/jour (ce qui est relativement peu) et qui met en moyenne 0,2 sec à être généré, pour une BDD d'environ 100 000 lignes. On pourrait dire que 0,2 sec c'est peu, mais je trouve que ca s'approche dangereusement de l'ordre de grandeur de la sec. Donc oui, je trouve que perso il faut que j'optimise.
Au niveau du tuning mysql j'ai fait ce que j'ai pu sur le peu de ressources que possède mon petit kimsufi à deux sous.

 

Pourquoi je souhaite optimiser la pagination? parce que ca m'arrive de travailler sur des sites qui font des millions de visiteurs et dont la bdd fait plusieurs fois 300 000 lignes. Et dans ce cas, l'optimistion est indispensable car sinon la charge mysql (et serveur) s'envole.
Donc c'est avant tout pour le principe d'optimisation que je cherche une solution, plutôt que résoudre un problème de perf sur cas donné  :)


Message édité par welcominh le 24-03-2010 à 20:31:53

---------------
Direct-download.com, le moteur de recherche pour Mega
n°1977109
rufo
Pas me confondre avec Lycos!
Posté le 25-03-2010 à 09:42:25  profilanswer
 

Je persiste à dire que tu peux gagner bien plus en perfs avec une meilleure indexation des champs (pour les jointures, par ex) et tuner MySQL. Utiliser Match Against à la place de like peut aussi être une solution. En faisant ça, sur ma BD de 300000 enregistrements, j'ai divisé par 5 les temps. :)


---------------
Astres, outil de help-desk GPL : http://sourceforge.net/projects/astres, ICARE, gestion de conf : http://sourceforge.net/projects/icare, Outil Cantine Calandreta : http://sourceforge.net/projects/canteen-calandreta
n°1977115
welcominh
Posté le 25-03-2010 à 09:56:18  profilanswer
 

Où vois-tu une quelconque jointure dans ma requete ?
J'ai déjà indexé tout ce qu'il fallait (je pense) dans ma table Livre, ainsi qu'au niveau tuning mysql. (Tu me diras, en même temps, sur une table comme ca ce n'est pas très dur).
 
Après confirmation des dires d'Oliiii, le LIKE tire bien parti des index.
J'ai deux points à optimiser: la sélection alphabétique et la pagination. J'ai optimisé le 1er point. Je cherche maintenant à optimiser le 2e, et tu me renvoies quand même vers le 1er ainsi que vers des jointures inexistantes. Désolé mais tu ne m'es pas vraiment d'une grande aide sur ce coup là  [:airforceone]


---------------
Direct-download.com, le moteur de recherche pour Mega
n°1977121
rufo
Pas me confondre avec Lycos!
Posté le 25-03-2010 à 10:07:34  profilanswer
 

Désolé, je pensais que la requête n'était pas complète vu qu'il y avait des "...".
 
Tiens voici un lien sur un script qui "analyse" les logs de ton mysql : http://www.day32.com/MySQL/
 
Après, il te propose un tuning. Sinon, tu peux déjà regarder les variables suivantes :
max length for sort data
max tmp tables   (mais surtout utile si y'a des jointures, donc là, pas utile a priori)
myisam max sort file size
myisam sort buffer size
query cache limit
query cache size
tmp table size (idem, utile si y'a des jointures)
 
Faut pas laisser ces variables avec les valeurs par défaut ;)


---------------
Astres, outil de help-desk GPL : http://sourceforge.net/projects/astres, ICARE, gestion de conf : http://sourceforge.net/projects/icare, Outil Cantine Calandreta : http://sourceforge.net/projects/canteen-calandreta
n°1977125
casimimir
Posté le 25-03-2010 à 10:17:48  profilanswer
 

je suis de l'avis de rufo et Oliii, je ne pense pas que tu y gagneras quelque chose, pour une simple raison, une fois les lignes identifiées (c'est le job de l'index) il va de toute façon les balayer toutes pour le order (et si il est malin réutiliser aussi l'index).
 
Si tu voulais implémenter une autre méthode tu serais de toute façon obligé de calculer un ordre et de le surselecter, et donc au final plus lourd.
 
a part si tes données n'étaient mise a jour que une fois par jour style minuit, la tu pourrais calculer un rank et taper un double index la dessus, mais la...

n°1977555
welcominh
Posté le 25-03-2010 à 22:01:10  profilanswer
 

J'ai pas compris la dernière ligne  :D  
Mais en gros, j'en conclus donc qu'il n'existe pas de réelle optimisation "simple" pour une pagination  [:airforceone]


---------------
Direct-download.com, le moteur de recherche pour Mega
n°1977630
Oliiii
Posté le 26-03-2010 à 08:40:38  profilanswer
 

Je suppose que tant que tu as un LIMIT, peut importe le nombre de record dans ta table il devrai arreter de lire apres les X premiers sans faire un table scan complet.
 
Un LIMIT 0, 10 ne devrai lire que les 10 premiers records, un LIMIT 10,10 ne devrai lire que 10 records aussi, donc la taille totale de ta table ne devrai avoir que peu d'influence tant que tu utilises un index.
 
Maintenance c'est a confirmer, je m'y connais bien en SQL Server, pas beaucoup en MySQL.

mood
Publicité
Posté le 26-03-2010 à 08:40:38  profilanswer
 

n°1977646
rufo
Pas me confondre avec Lycos!
Posté le 26-03-2010 à 09:46:59  profilanswer
 

Sauf qu'il a un order by, donc faut bien scanner l'ensemble des enregistrements trouvés, les trier et ensuite ramener que ces dans le LIMIT.
 
Ce que voulait dire casimimir c'est que si tes données ne sont modifiées qu'une fois dans la journée à une heure connue, il est possible de calculer un second index portant sur le tri (ou la pertinence) des résultats qui permettra d'accélérer la requête. C'est un comme si tu essayait d'estimer les requêtes de recherche les plus probables ou les plus utilisées par les utilisateurs et qu'une fois par jour (la nuit de préférence), tu pré-calculais les résultats de recherche correspondant à ces recherches et que tu les triais + paginais déjà. Quand un utilisateur fera une recherche sur l'une de ces recherches pré-calculer, y'aura juste à récupérer les enregistrements déjà triés et "limités" ;)


---------------
Astres, outil de help-desk GPL : http://sourceforge.net/projects/astres, ICARE, gestion de conf : http://sourceforge.net/projects/icare, Outil Cantine Calandreta : http://sourceforge.net/projects/canteen-calandreta
n°1977688
welcominh
Posté le 26-03-2010 à 11:25:09  profilanswer
 

Une sorte de cache donc ?


---------------
Direct-download.com, le moteur de recherche pour Mega
n°1977693
rufo
Pas me confondre avec Lycos!
Posté le 26-03-2010 à 11:26:37  profilanswer
 

un peu oui, mais un cache en BD et un cache qui contient des infos pré-calculées (anticipées, donc) et non des données déjà demandées par les utilisateurs.


---------------
Astres, outil de help-desk GPL : http://sourceforge.net/projects/astres, ICARE, gestion de conf : http://sourceforge.net/projects/icare, Outil Cantine Calandreta : http://sourceforge.net/projects/canteen-calandreta
n°1977732
Oliiii
Posté le 26-03-2010 à 12:28:57  profilanswer
 

Si l'index est crée sur nom_livre il ne devra rien trier du tout avant d'utiliser le LIMIT, c'est le boulot de l'index de garder tout bien trié :)

n°1977774
casimimir
Posté le 26-03-2010 à 14:28:41  profilanswer
 

je ne serai pas aussi catégorique, je ne joue pas en mysql mais uniquement en oracle.
 
prenons une table de 5.000.000 d'enregistrement, un champ primary key champ_pk,
 
si je fais ceci j'ai un résultat immédiat a l'écran, le pe indique qu'il utilise bien l'index

Code :
  1. SELECT champ_pk
  2. FROM TABLE
  3. ORDER BY champ_pk


si je fais ceci ca prend une plombe il fait un full table scan

Code :
  1. SELECT champ_pk, champ_1
  2. FROM TABLE
  3. ORDER BY champ_pk


 
donc il faut faire le test pour voir comment mysql se comporte réellement.

n°1977786
Oliiii
Posté le 26-03-2010 à 14:46:45  profilanswer
 

Ta primary key n'est probablement pas un clustered index, donc il passe son temps a faire des lookups.
Ca n'a pratiquement plus aucun effect quand tu fais un TOP xxxx (ou un LIMIT pour MySQL), vu qu'il ne fera un lookup que sur xxxx rows maximum.
 
Le mieux est evidement d'avoir ta primary key clustered, ca donne ca (primary key STARTDATE et TechKey):

Code :
  1. -- PK only
  2. SELECT top 100 STARTDATE, TechKey
  3. FROM Pm_cdrdatatbl
  4. ORDER BY STARTDATE, TechKey
  5. --Table 'Pm_cdrdatatbl'. Scan count 1, logical reads 22, physical reads 0, read-ahead reads 3, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  6.  
  7. --SQL Server Execution Times:
  8. --   CPU time = 0 ms,  elapsed time = 2 ms.
  9.  
  10. -- PK + 1 field
  11. SELECT top 100 STARTDATE, TechKey,CAUSE
  12. FROM Pm_cdrdatatbl
  13. ORDER BY STARTDATE, TechKey
  14. --Table 'Pm_cdrdatatbl'. Scan count 1, logical reads 22, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  15.  
  16. --SQL Server Execution Times:
  17. --   CPU time = 0 ms,  elapsed time = 2 ms.


 
Rowcount: 214.405.920 (44GB)


Message édité par Oliiii le 26-03-2010 à 16:05:04
n°1978101
welcominh
Posté le 28-03-2010 à 13:16:17  profilanswer
 

Euh c'est à qui parlera le plus technique et qui arrivera le plus à m'embrouiller ou bien ?  :D


---------------
Direct-download.com, le moteur de recherche pour Mega

Aller à :
Ajouter une réponse
  FORUM HardWare.fr
  Programmation
  SQL/NoSQL

  Optimiser une pagination ?

 

Sujets relatifs
Optimiser code PHPPagination sans LIMIT
Optimiser l'historique de visionnage d'une vidéothèque[Résolu]Bouton <Précédent> <Suivant> Pagination
[Résolu]VBA comment optimiser l'utilisation CPU dans un loop?PB Count (Résolu) et un souci de lien de pagination
Encore une question sur la pagination dans une feuille Excel[dreamwaver] Optimiser son site pour la résolution d'écran
Problème de pagination[Excel & Access] Optimiser la communication entre les deux
Plus de sujets relatifs à : Optimiser une pagination ?


Copyright © 1997-2018 Hardware.fr SARL (Signaler un contenu illicite) / Groupe LDLC / Shop HFR