Simplification d'une requête -> LIKE '%mot%' (temps de traitement de la requête trop longue)

**Devina** · 17/04/2015, 09h39

Bonjour,

Voici mon petit problème de newbie : j'ai une requête qui lors de son traitement sur phpMyAdmin a un temps de chargement trop long. Et je voudrais savoir s'il était possible de simplifier cette requête au niveau du "LIKE '%mot%'".

Je vous met ci-dessous mon code,

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
----------------------------------------------------------------------------------------------------------
 
SELECT distinct page.idPage, page.titre, page.ident, page.date_page, page.date_publication, mot.mot, synonyme.synonyme, type.type, theme.theme
FROM page, page_mot, mot, synonyme, type, page_theme, theme
WHERE page.idPage = page_motcle.eid_page
AND page_mot.eid_mot = mot.idMot
AND motcle.idMot = synonyme.eidMot
AND page.eid_type= type.idType
AND page.idPage = page_theme.eid_page
AND page_theme.eid_theme = theme.idTheme
AND (page.titre like '%1980%'
	OR page.ident like '%1980%'
	OR page.date_page like '%1980%'
	OR page.date_publication like '%1980%'
	OR mot.mot like '%1980%'
	OR synonyme.synonyme like '%1980%'
	OR type.type like '%1980%'
	OR theme.theme like '%1980%')
 
----------------------------------------------------------------------------------------------------------

Je l'ai simplifié avec un vrai terme. Sinon à la place de "1980" il y a récupération d'une variable par le biais d'un formulaire ($_POST['variable']) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
 
/*----------------------------------------------------------------------------------------------------------*/
 
// Code réel
// on récupère et on vérifie le POST avant
if( $tab_size>0 ){		
     $message ="SELECT distinct page.idPage, page.titre, page.ident, page.date_page,
                                           page.date_publication, mot.mot, synonyme.synonyme,type.type,  theme.theme
			FROM page, page_mot, mot, synonyme, type, page_theme, theme
			WHERE page.idPage = page_mot.eid_page
			AND page_mot.eid_mot = mot.idMot
			AND mot.idMot = synonyme.eidMot
			AND page.eid_type= type.idType
			AND page.idPage = page_theme.eid_page
			AND page_theme.eid_theme = theme.idTheme
			AND (page.titre like '%".$tab_words[0]."%'
				OR page.ident like '%".$tab_words[0]."%'
				OR page.date_page like '%".$tab_words[0]."%'
				OR page.date_publication like '%".$tab_words[0]."%'
				OR mot.mot like '%".$tab_words[0]."%'
				OR synonyme.synonyme like '%".$tab_words[0]."%'
				OR type.type like '%".$tab_words[0]."%'
				OR theme.theme like '%".$tab_words[0]."%')";
 
	for($i=1 ;$i<$tab_size ;$i++ ){
		$joint[$i]="AND page.titre like '%".$tab_words[$i]."%'";
		$joint2[$i]="OR page.ident like '%".$tab_words[$i]."%'";
		$joint3[$i]="OR page.date_page like '%".$tab_words[$i]."%'";
		$joint4[$i]="OR page.date_publication like '%".$tab_words[$i]."%'";
		$joint5[$i]="OR mot.mot like '%".$tab_words[$i]."%'";
		$joint6[$i]="OR synonyme.synonyme like '%".$tab_words[$i]."%'";
		$joint7[$i]="OR type.type like '%".$tab_words[$i]."%'";
		$joint8[$i]="OR theme.theme like '%".$tab_words[$i]."%'";
	}
	foreach ($joint as $value){
		$message.=$value;
	}
	foreach ($joint2 as $value2)
	{
		$message.=$value2;
	}
	foreach ($joint3 as $value3)
	{
		$message.=$value3;
	}
	foreach ($joint4 as $value4)
	{
		$message.=$value4;
	}
	foreach ($joint5 as $value5)
	{
		$message.=$value5;
	}
	foreach ($joint6 as $value6)
	{
		$message.=$value6;
	}
	foreach ($joint7 as $value7)
	{
		$message.=$value7;
	}
	foreach ($joint8 as $value8)
	{
		$message.=$value8;
	}
}
 
// send request
$req_word = $connect->query($message);
 
// recovery of results
$row = $req_word->fetchAll(PDO::FETCH_ASSOC);
var_dump($row);
/*----------------------------------------------------------------------------------------------------------*/

Merci pour votre aide

**CinePhil** · 17/04/2015, 10h22

1) Les jointures s'écrivent depuis plus de 20 ans avec l'opérateur JOIN ; il serait temps de s'y mettre !
Voir les cours et tutoriels de formation pour apprendre SQL.

2) La requête est longue parce que le SGBD ne peut pas utiliser d'index avec une restriction du genre [codeinline]WHERE une_colonne LIKE '%du_texte%'.
Vous pouvez utiliser la recherche FULL TEXT. Chercher ces mots clés en rapport avec votre SGBD.

Ceci dit, est-ce que, au minimum, vos tables sont correctement indexées pour que les jointures soient rapides ?

**ZenZiTone** · 17/04/2015, 10h43

Envoyé par CinePhil

1) Les jointures s'écrivent depuis plus de 20 ans avec l'opérateur JOIN ; il serait temps de s'y mettre !

C'est un problème récurrent apparemment

Sinon, plutôt que de multiplier les "OR", ne serait-il pas possible de faire le test sur la concaténation de l'ensemble des valeurs d'une ligne?

Quelque chose du genre :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

WHERE CONCAT(page.ident, page.date_page, page.date_publication, mot.mot, synonyme.synonyme, type.type, theme.theme) like '%1980%'

Je n'ai jamais réellement fait d’optimisation de code ou de requête, mais le fait de réduire le nombre de "tests" pourrait fonctionner non?

**bstevy** · 17/04/2015, 11h01

C'est pas les tests qui sont longs, c'est le fait de devoir scanner toute la table pour récupérer toutes les données et les analyser qui plombe le temps de traitement.

et le seul moyen de pas tout scanner, c'est d'avoir des index... mais avec like, difficile ^^

**ZenZiTone** · 17/04/2015, 11h08

Envoyé par bstevy

C'est pas les tests qui sont longs, c'est le fait de devoir scanner toute la table pour récupérer toutes les données et les analyser qui plombe le temps de traitement.

et le seul moyen de pas tout scanner, c'est d'avoir des index... mais avec like, diffice ^^

De toutes façon toute la table doit être scannée si on ne sait pas dans quelle colonne se trouve le mot à chercher non?

**bstevy** · 17/04/2015, 11h18

Tu prends les miserables, le gros bouquin de victor hugo, et tu cherches le mot "pioche" ^^

On va voir combien de temps tu mets mdr

oui, c'est un peu le problème de ton cas.

mais les experts auront peut etre de brillantes solutions.

**CinePhil** · 17/04/2015, 11h23

C'est pour ça que j'ai indiqué la piste de la recherche FULL TEXT car ainsi l'index FULL TEXT peut être utilisé.
Sinon il est possible aussi de modéliser sa propre indexation des mots des colonnes lors de l'enregistrement des informations.

Il peut être pertinent aussi d'analyser ce qui est recherché et de ne pas interroger systématiquement dans toutes les colonnes. L'exemple donne une recherche sur 1980 qui peut être une année ou une quantité mais il ne serait par exemple par pertinent de chercher un texte sur une colonne numérique ou de type date ou datetime ou timestamp. Ça réduit la recherche et la requête peut ainsi être simplifiée.

**escartefigue** · 17/04/2015, 12h07

Quel est la volumétrie de la table ?
Quel est la périodicité du traitement qui effectue cette recherche ?
Le traitement doit il s'exécuter en concurrence avec d'autres ou hors plages d'utilisation de la BDD ?

**Devina** · 17/04/2015, 16h55

Tout d'abord merci de vos messages très rapides.
Je m'aperçois qu'il fallait vraiment que je mette à jour mes connaissances, merci d'avoir pointé le doigt sur les éléments que je devais travailler (moderniser mon sql et ne pas utiliser de LIKE).

1) Les jointures s'écrivent depuis plus de 20 ans avec l'opérateur JOIN ; il serait temps de s'y mettre !
En effet, je m'aperçois que ce qu'on m'a appris lors de mes études est maintenant obsolète, le INNER JOIN marche à merveille. J'aurai du mieux me renseigner sur les jointures.

2) La requête est longue parce que le SGBD ne peut pas utiliser d'index avec une restriction du genre [codeinline]WHERE une_colonne LIKE '%du_texte%'.
Vous pouvez utiliser la recherche FULL TEXT. Chercher ces mots clés en rapport avec votre SGBD.
Mes tables ont bien étés indexés, j'ai utilisé la recherche FULLTEXT qui est très rapide en utilisant le MATCH(champs) AGAINST('mot_a_chercher') qui m'a permis d'avoir le traitement que je voulais sur mes tables.

3) Quel est la volumétrie de la table ?
Table assez imposante de données, elle dépasse les 500 éléments et voir plus suivant le temps...

4) Quel est la périodicité du traitement qui effectue cette recherche ?
Suivant la recherche des users. Cette requête est faite pour être utilisée dans un système de "moteur de recherche" qui va vérifier les éléments rentrés par les users sur la database.

5) Le traitement doit il s'exécuter en concurrence avec d'autres ou hors plages d'utilisation de la BDD ?
J'admet, je n'ai pas très bien compris la question

Voici ma requête qui pour le moment fonctionne mais qui doit surement être amélioré :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
SELECT distinct page.titre, page.ident, page.date_page, page.date_publication, mot.mot, synonyme.synonyme, type.type, theme.theme
FROM page
LEFT JOIN page_mot
ON page.idPage = page_mot.eid_page
LEFT JOIN mot
ON page_mot.eid_mot = mot.idMot
LEFT JOIN synonyme
ON mot.idMot = synonyme.eidMot
LEFT JOIN type
ON page.eid_type= type.idType
LEFT JOIN type_theme
ON page.idPage = page_theme.eid_page
LEFT JOIN theme
ON page_theme.eid_theme = theme.idTheme
WHERE MATCH(page.titre) AGAINST('1968')
OR MATCH(page.ident) AGAINST('1968')
OR MATCH(type.type) AGAINST('1968')
OR MATCH(mot.mot) AGAINST('1968')
OR MATCH(synonyme.synonyme) AGAINST('1968')
OR MATCH(theme.theme) AGAINST('1968')
OR page.date_page like '%1968%'
OR page.date_publication like '%1968";

N'hésitez pas à me donner des conseils si vous voyez des éléments à améliorer ou des éléments qui sont vraiment "horrible".
Merci

**CinePhil** · 17/04/2015, 18h16

3) Quel est la volumétrie de la table ?

Table assez imposante de données, elle dépasse les 500 éléments et voir plus suivant le temps...

LOL ! C'est minuscule, ça !

Une grosse table de BDD, ça commence à se compter en centaines de milliers de lignes, voire en millions.

**ZenZiTone** · 18/04/2015, 01h24

Envoyé par Devina

1) Les jointures s'écrivent depuis plus de 20 ans avec l'opérateur JOIN ; il serait temps de s'y mettre ![/U]
En effet, je m'aperçois que ce qu'on m'a appris lors de mes études est maintenant obsolète, le INNER JOIN marche à merveille. J'aurai du mieux me renseigner sur les jointures.

Pour info j'ai un cours de SQL durant mes études l'année dernière et c'était tout-aussi obsolète (étant donné que les requêtes étudiées en cours sont généralement de petite taille, on se rend moins compte de l'impact du JOIN - au passage, merci CinePhil pour l'exemple

)

concernant ta requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

WHERE MATCH(CONCAT(liste de tes colonnes)) AGAINST('1968')

serait-il plus performant?

**Deuzz** · 18/04/2015, 11h28

Envoyé par bstevy

Tu prends les miserables, le gros bouquin de victor hugo, et tu cherches le mot "pioche"

6ème mot ligne 14

http://gallica.bnf.fr/m/ark:/12148/b...xtePage.langFR

**bstevy** · 20/04/2015, 01h54

Envoyé par Deuzz

6ème mot ligne 14

http://gallica.bnf.fr/m/ark:/12148/b...xtePage.langFR

OK lol
t'as mis 24h ^^

Simplification d'une requête -> LIKE '%mot%' (temps de traitement de la requête trop longue)

Langage SQL

Discussions similaires

Partager

Partager