mysql optimisation bizarre d'une table avec plusieurs index

+2 votes
10,402 vues
question 4 Janvier 17 dans Sql Phpmyadmin par saintjoseph (192 points)  
editée 4 Janvier 17 par ladmin

Tout d'abord, merci de mettre à disposition la liste des requêtes lentes

J'ai un problème avec l'optimisation de cette requête

select * from `table` when `col1` = 1 and `col2`=1 and `col3` = 1 ; // table contient 1000.000 lignes ,

// `col1`=1 : vérifié par 15000 lignes
// `col2`=1 : vérifié par 1000 lignes
// `col3`=1: vérifié par 28000 lignes
// `col1` = 1 and `col2`=1 and `col3` = 1 : vérifié par 1 ligne

et pourtant mysql a scruté 15000 lignes selon les logs de la table `0slowsql`

Pourquoi ? Normalement, il doit croiser les 3 index et trouver la ligne directement. C'est ce que feraient toutes les bases de données que j'utilise par ailleurs depuis 20 ans. Est ce pareil dans la version pro Mysql Enterprise ? Que faire pour utiliser Mysql Enterprise ?

 

commentée 4 Janvier 17 par ladmin (2,277 points)  
Mais non, ce n'est pas ce que font les autres bases, M.E. non plus. On peut s'organiser contre ce "désagrément" mais ce n'est pas une optimisation naturelle. Peut être quelqu'un répondra sur le fond ...
commentée 4 Janvier 17 par saintjoseph (192 points)  
Je ne peux pas attendre, c'est la requête principale de ma base. Merci. C'est que j'en ai d'autres.
commentée 4 Janvier 17 par ladmin (2,277 points)  
j'ai édité le titre de la question en conservant vos mots ...

1 Réponse

+2 votes
réponse 4 Janvier 17 par ladmin (2,277 points)  
editée 5 Janvier 17 par ladmin

Les progs de BD traitent ce genre de requêtes en prenant la selection du 1er index existant après le where , puis en testant ligne par ligne les autres conditions. L'auteur de la requête peut en tirer profit s'il sait utiliser l'index le plus restrictif en premier. Ce n'est pas toujours possible surtout quand les requêtes sont construites d'après un formulaire multi-options.

Slowsql est simplement la table des logs slow-sql.log de Mysql. Ces logs rapportent le cardinal du 1er index ( ici 15000 ). Mais ils ne disent pas l'effort que Mysql a fait pour analyser cet index et en extraire le tas de 15000. Cet effort est loin d'être négligeable. Il dépend de la répétitivité des index. Là, le prog de BD ne dispose pas , du moins en INNODB et MYISAM, de compteurs tenus à jour en temps réel. Comme il faut les calculer, il se contente donc du 1er. Statistiquement, dans les applis de type site web, n'utiliser qu'un index est plus efficace. Les caches-mémoires et les grosses ressources allègent la charge jusqu'à la rendre acceptable dans tous les cas.

On peut le regretter mais si on fournit au prog de BD un long algo, il y passera beaucoup de temps. Donc, évitez cette requête si son intérêt est mineur ou bien alors dessinez une autre base avec assez de résultats intermédiaires pour que la recherche pointe directement vers le plus petit paquet.

Il y a ici une première modification simple efficace qui consiste à créer un index multicolonnes col1,col2,col3. Il sera utilisé en priorité.

Dans des cas plus compliqués, il ne faut pas hésiter à ajouter et tenir à jour des colonnes rendant directement les résultats escomptés après les where. C'est d'ailleurs la seule façon de faire lorsque la condition ressemble à

select * from table where col1+col2 = 5 and col3=18

Pour une efficacité maximale , il faudrait créer une colonne col4 qui vaut toujours concat(col1+col2 , séparateur , col3 ) et en faire un index.

Si le séparateur choisi est '/' , cela donnerait cette nouvelle requête plus rapide :

select * from table where col4 = '5/18'

 

Pour une approche totale des colonnes de calcul intermédiaire, il ne faut pas manquer la page sur les triggers dans la documentation mysql

 

commentée 4 Janvier 17 par saintjoseph (192 points)  
j'essaye maintenant, merci
commentée 4 Janvier 17 par saintjoseph (192 points)  
l'index triple marche. J'ai fait explain la requête dans phpmyadmin. Maintenant, le nombre de lignes trouvées est juste la solution. Ca se voit sur le site. Merci encore.
commentée 5 Janvier 17 par ladmin (2,277 points)  
J'ai ajouté le lien vers les triggers dans la doc Mysql
commentée 5 Janvier 17 par anvers (269 points)  
en effet, avec les triggers, on fait ce qu'on veut sans retoucher les sql d'insertion, ce qui est parfois difficile avec des modules tiers. Mais ce n'est déjà plus du travail d'amateur.
commentée 7 Janvier 17 par debo (228 points)  
Je ne comprends pas très bien les triggers. C'est grave, docteur ?
commentée 7 Janvier 17 par ladmin (2,277 points)  
Pourquoi ne pas poser une question explicite ?
Feel free to ask and answer in english

Bienvenue sur les FAQ de 123.fr, posez vos questions ou répondez à celles des autres usagers.

...