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":
 

 
 
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.  

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.

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).

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é  

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.

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à  

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

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...

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

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.

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"

Une sorte de cache donc ?

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.

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é

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

SELECT champ_pk
FROM TABLE
ORDER BY champ_pk

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

SELECT champ_pk, champ_1
FROM TABLE
ORDER BY champ_pk

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

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):

-- PK only
SELECT top 100 STARTDATE, TechKey
FROM Pm_cdrdatatbl
ORDER BY STARTDATE, TechKey
--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.
 
--SQL Server Execution Times:
--   CPU time = 0 ms,  elapsed time = 2 ms.
 
-- PK + 1 field
SELECT top 100 STARTDATE, TechKey,CAUSE
FROM Pm_cdrdatatbl
ORDER BY STARTDATE, TechKey
--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.
 
--SQL Server Execution Times:
--   CPU time = 0 ms,  elapsed time = 2 ms.

 
Rowcount: 214.405.920 (44GB)

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