Requete avec Substring très lente ( temps d'exécution dépasse 1minute) [Résolu]

[sgdev]

Bonsoir,
J'ai un problème de performance avec une requête sql.

Mon script utilise deux tables :
table contenant 2 champs : id_table, num_table
table_bis contenant 2 champs : id, num

(ci-joint une image qui décrit la requête que je voudrai construire).

Ma requête actuelle :

Code :

1
2
3
4
5
SELECT  table_1.id_table, table2.id_table ,table_bis.ID
FROM TABLE table_1, TABLE table_2 , table_bis
WHERE table_1.num_table = table_2.num_table 
AND table_bis.ID = table_2.id_table
AND table_1.id_table = 1000 ( par exemple)

"table" contient 300 000 enregistrements, la requête s’exécute sans aucun problème.

Je voudrai modifier la requête pour intégrer une condition sur les 4 premiers caractères du champs num_table (au lieu de tester sur tout le champ num_table) ,j'ai utilisé la méthode substring dans la nouvelle requête .

Nouvelle requête :

Code :

1
2
3
4
5
Select  table_1.id_table, table2.id_table ,table_bis.ID
From table table_1, table table_2 , table_bis
Where substring(table_1.num_table,1,4)   = substring(table_2.num_table , 1, 4)
And table_bis.ID = table_2.id_table
And table_1.id_table = 1000

Problème : l’exécution de la requête prend au moins une minute, ce qui est énorme.
Avez-vous une solution a mon problème, est-ce qu’il y a un moyen d’optimiser cette requête.

Merci d'avance pour votre aide (Je remercie aussi ceux qui ont pris la peine de visiter mon post )
Youness

[Madfrix]

Bonsoir. Essaies déjà la même requête en effectuant une vraie jointure et pas un produit cartésien pour voir si cela change quelque chose :

Code sql :

1
2
3
4
5
6
7
 
SELECT table_1.id_table FROM table_1 T1
INNER JOIN table2 T2
ON substring(T1.num_table, 1, 4)  = substring(T2.num_table, 1, 4)
INNER JOIN table_bis T3
ON T3.ID = T2.id_table
AND T1.id_table = 1000

[sgdev]

Je te remercie pour ta réponse.
La requête normal sans substring s'exécute en 0.7 seconde.
ma requête prend 77 secondes, et la tienne 70s (moyenne sur quelques requêtes) seconde.
y a-t-il une autre solution pour optimiser cette requête?
Merci d'avance

[Madfrix]

Oui, les index. Tes tables ne doivent pas en avoir notamment sur num_table

[sgdev]

J'ai bien un index sur les num_table et num, et les id sont des primary key

Moi, la seule piste que j'ai ,c'est de créer une nouvelle colonne num_table_bis dans "table", qui contiendra la valeur du champ num_table sur 4 caractères. mais je dois modifier énormément de traitement dans mon appli pour pouvoir mettre à jour ce champs.

Merci pour ton aide, n'hésites pas si tu as d'autres solutions.

[Madfrix]

Je me suis constitué vite fait une table de 100k enregistrements et une requête similaire se fait en moins d'une seconde substring() ou non.

Peux tu faire un :

Code :

1
2
 
SHOW CREATE TABLE nom_table;

sur chacune des tables et nous montrer ce que tu obtiens ?

[sgdev]

toutes les tables suivent le même model :
exemple table :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
 
CREATE TABLE `matable` (
 `id` int(11) NOT NULL DEFAULT '0',
 `id_table` int(11) NOT NULL DEFAULT '0',
 `num_table` int(11) NOT NULL DEFAULT '0',
 .... une 10 ene d'autres champs
 PRIMARY KEY  (`id`),
 KEY `id_table` (`id_table`),
 KEY `num_table` (`num_table`),
 KEY `id_table_2` (`id_table`,`id_statut`),
 KEY `.....
 .....
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Ma requete contient d'autres conditions sur d'autres champs des conditions de types champ is not null ( avec index sur le champ), champ = valeur...

Je pense pas que ces conditions rendent la requête plus complexe ( sauf s'il faut les mettre dans un ordre bien précis), j'ai pas mis tout le script pour ne pas compliquer la compréhension du problème, et je rectifie ...ma table contient vers les 600000 enregistrements.

[qi130]

La lenteur constatée vient probablement de la comparaison sur les 4 caractères.

De fait, cette comparaison exige de transtyper la colonne numérique en chaine, puis d'extraire les 4 caractères, puis de comparer avec une autre valeur obtenue par les mêmes opérations.

Dès lors, les index ne sont pas utilisés.

Une alerte: je ne sais pas comment tu feras avec les valeurs 14500 et 1450 !

[Madfrix]

Bonjour,

certes le substring() doit faire ralentir...mais 70s !!
Sur un jeu de test perso, c'est beaucoup plus rapide chez moi. Peux tu nous montrer ta requête complète ?

[sgdev]

Merci pour vos réponse
En effet après quelques tests, j'ai constaté la problème est posé lorsque j'intègre une 3 eme table dans dans ma requête.
Ci-dessous la requête complète (voir PJ)

Code :

1
2
3
4
5
6
7
8
9
10
11
 
SELECT DISTINCT T3.num, T1.id_table, T2.id_table,T4.id_type,T4.Libelle_type FROM table_1 T1
INNER JOIN table2 T2
ON substring(T1.num_table, 1, 4)  = substring(T2.num_table, 1, 4)
INNER JOIN table_bis T3
ON T3.ID = T2.id_table
AND T1.id_table = 1000
AND T1.num_table IS NOT NULL
AND T1.num_table !=''
INNER JOIN table_type T4 ON (T4.id_type = T2.id_type)
ORDER BY T3.num

l'exécution du script sans la partie inner join sur la table T4 prend 5s, par contre avec la T4, elle prend 100s
La T4 contient une 10 ene de ligne, avec un primary key sur l'id

@qi130 : en ce qui concerne le cas d'exemple 14500 et 1450 , un substring me permettra de faire un test que sur les 4 premiers caractères, les autres caractères non pas d'importance (j'utilise un distinct dans ma requête )
Merci

[Madfrix]

Essaies ceci pour voir :

Code sql :

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT DISTINCT T3.num, T1.id_table, 2.id_table,T4.id_type,T4.Libelle_type FROM table_1 T1
INNER JOIN table2 T2
ON substring(T1.num_table, 1, 4)  = substring(T2.num_table, 1, 4)
INNER JOIN table_bis T3
ON T3.ID = T2.id_table
INNER JOIN table_type T4 
ON T4.id_type = T2.id_type
WHERE T1.id_table = 1000
AND T1.num_table IS NOT NULL
AND T1.num_table != ''
ORDER BY T3.num

[sgdev]

ca donne rien
temps d'exécution :

- avec substring :104s
- sans substring : rapide , 1s

Merci

[Madfrix]

As tu essayé avec un LEFT ?

Code :

1
2
3
4
 
...
ON LEFT(T1.num_table, 4)  = LEFT(T2.num_table, 4)
...

[sgdev]

Je viens de tester avec LEFT, temps d'exécution = 104 ( aucune différence avec substring)
est-ce que l'ordre des INNER join et des conditions est important?

[Madfrix]

Tu peux tester de remplacer chaque INNER JOIN par STRAIGHT_JOIN en effectuant la jointure avec substring en dernier pour voir

[sgdev]

non, aucune amélioration
est-ce que le problème peut être causé par un problème de limite d'espace disque, de cache...?

[gene69]

Si substring pose des problèmes débarasse -en toi !

A condition d'avoir des triggers, on a rien en partant de rien.

Tu précalcules et tu maintiens une colonne "invisible" qui est maintenue par les trigger que tu indexes et c'est fini des problèmes de jointure lente pour les problèmes sus-cités.

Bonne idée ou mauvaise idée?

[Madfrix]

Bizarre quand même cette histoire de passer d'un facteur 1 à 100 juste par l'ajout d'une fonction qui plus est rapide logiquement

[sgdev]

Bonsoir,
La seule solution qui me reste c'est de maintenir une nouvelle colonne que je mettrai à jour avec les 4 premiers caractères du champ id_num.
L'utilisation d'un trigger sera très couteux puisqu'à chaque création/modification dans la table je dois faire un update sur toutes la tables ( ce qui est énorme vu le nombre d'enregistrements de la table).
je prévois de mettre à jour à nouvelle colonne à chaque insertion/update dans mon appli, ca m'évitera de faire des update en masse avec les triggers.

[sgdev]

une autre solution, sera de découper la requête en deux parties. et passer le résultat de la première requête à la 2 eme en PHP.