Bonjour,
 
je viens à vous pour un problème de lenteur sur une requête sql sur un serveur apache/php/mysql.
 
Je vous explique l'agencement de la chose,
J'ai une table avec une liste de societe [id/nom/type/ordre] avec des index sur chaque champ même le nom qui est un varchar. Le type étant un enum.
Il y a une table secondaire societe_adresse [id/id_societe/adresse/code_postal] avec des index sur les champs id et id_societe
 
Il y a dans chaque table environ 400.000 entrées.
 
Et voici ma requête lente (entre 3 et 6 secondes) :
SELECT SQL_NO_CACHE s.id, s.nom, sa.code_postal
FROM societe s
LEFT JOIN societe_adresse sa ON sa.id_societe = s.id
WHERE s.type = '0' AND (s.nom LIKE 'de%' OR s.nom LIKE '% de%')
ORDER BY s.ordre DESC
 
Le sql_no_cache est là pour avoir le temps réel des requêtes.
 
Je ne comprend pas sachant que tous les champs sont indexés même le nom.
Et que lorsque j'enlève le "ORDER BY" ou lorsque j'enlève dans le where la recherche sur le type uniquement, la requête est rapide (0.001 secondes)
 
Voilà le explain de la requête lente :
+----+-------------+-------+------+---------------+------------+---------+-------------------+--------+-----------------------------+
| id | select_type | table | type | possible_keys | key         | key_len | ref                   | rows     | Extra                            |
+----+-------------+-------+------+---------------+------------+---------+-------------------+--------+-----------------------------+
|  1 | SIMPLE      | s       | ALL  | nom,type       | NULL       | NULL     | NULL                | 343164 | Using where; Using filesort |
|  1 | SIMPLE      | sa     | ref   | id_societe      | id_societe | 4         | fashionjcv_2.s.id |      1    |                                    |
+----+-------------+-------+------+---------------+------------+---------+-------------------+--------+-----------------------------+
 
J'ai testé beaucoup de possibilités et celle que j'ai pour l'instant retenu est de placer le where du type en HAVING s.type = 0, la requête est ainsi relativement rapide
mais je ne suis pas serein et je ne comprend pas pourquoi ma première requête est lente...
 
Si vous aviez une piste à me donner je vous en serais reconnaissant.

En SQL, il est impossible d'utiliser, pour une même occurence d'une table, plus de 1 index en simultané.
 
Je te conseille donc d'écrire un index sur le tuple (type, nom) si tu veux que tes index soient réellement utiles.
 
Ensuite, un LIKE, c'est malheureusement pas top pour les index. Autant le "like 'de%'" devrait utiliser l'index, autant le second like ne peut absolument pas utiliser l'index.
 
Ensuite, tu as un left join (LEFT OUTER JOIN c'est plus clair). Est-ce bien utile ? Peux-tu avoir des sociétés sans adresses ? Et ces sociétés d'intéressent-elles ? Un INNER JOIN devrait améliorer un peu les perfs.

Merci de ta réponse Magic,
 
"En SQL, il est impossible d'utiliser, pour une même occurence d'une table, plus de 1 index en simultané."
je ne suis pas sur de ca, c'est justement l'intêret d'avoir plusieurs indexations sur une table.
sur la doc mysql : "Si un index multi-colonne existe sur les colonnes col1 et col2, les lignes appropriées seront directement lues. Si des index séparés sur les colonnes col1 et col2  existent, l'optimiseur va essayer de trouver l'index le plus restrictif des deux, en décidant quel index débouche sur le moins de lignes possibles."
 
J'ai regardé pour les index et en ayant un index (type, nom) ca ne change pas la vitesse de la requête.
 
Par contre ton explication sur le like '% de%' est effectivement bonne, en l'enlevant je retrouve bien un type à 'range' lors d'un explain et non un all. Il utilise donc bien l'index.
Je vais donc devoir me créer une indexation par lettre à la main...
 
LEFT JOIN et LEFT OUTER JOIN, c'est pareil non ? j'économise juste 6 octets.
Et oui je peux avoir des sociétés sans adresse d'ou le left join. J'aurais mis un inner à la place sinon
 
Merci en tout cas je vais voir pour me passer du like qui pose problème.

RATAL
tu as dans ton where une condition sur ta table s, ton left outer join se transforme implicitement en inner join ( le truc des 6 octets c'est nawak en passant).
Ta requete ne remontera jamais les sociétés sans adresse.

Par ailleurs quel sgbd utilises-tu?

Par exemple sur mysql les perfs sont dramatiques avec like %.
Il est absolument nécessaire de passer par un index fulltext et l'utilisation de match against pour trouver un niveau de perf convenable.

anajapari > euh... c'est sur "sa" que ça poserait problème, mais sur s, y'a pas de problème...
 
tomefa > la doc dit justement ce que j'ai dit à propos des index : un seul index est utilisé. et un index multi-colonne, lorsqu'il est utilisé, est toujours pus performant qu'un index uni-colonne + un filtre sur les valeurs du second champ.
 
Quant aux 6 octets gagnez euh... renomme toutes tes variables, fonctions, tables et champs en lettres, tu gagneras bien plus encore... Par contre si t'arrive à piger ce que fait ton programme ensuite...
La lisibilité, c'est le plus important dans un programme. Au moment de l'exécution, tu peux avoir 500 Mo de commentaires et des variables de 500 caractères de long, ça changera pas d'une µseconde le temps d'exécution. Par contre le gars qui repasse derrière toi, il comprend ce que tu fais (enfin... peut-être pas avec autant de commentaires et des variables aussi longues mais bon )
 
Il faut toujours rester le plus explicite possible. Dans un left outer join, right outer join ou full outer join, le plus important à la base, c'est le outer : je peux avoir des null qui déboulent de nulle part. Après qu'on ramène les sociétés sans adresses, les adresses sans société, ou les deux, c'est bien moins important.

ça m'apprendra à lire trop vite