Discussion :

[defacta]

Bonjour,

J'ai une requête qui marchait bien tant qu'il n'y avait pas beaucoup de lignes dans ma base de données, mais avec 150 000 lignes dans la table users et 18 000 dans la table users_oauth, ma requête prend maintenant plus de 3 minutes à s'exécuter.

Au niveau du site, j'ai un champs de recherche qui permet de chercher dans plusieurs champs de la table users, par exemple, ma requête peut ressembler à ceci si on recherche gmail:

Code :

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

1
2
3
4
5
6
7
8
SELECT users.*, users_oauth.* FROM users LEFT JOIN users_oauth ON users.user_id = users_oauth.oauth_user_id WHERE
 
                                 users.user_id LIKE '%gmail%' OR
                                 users.user_firstname LIKE '%gmail%' OR
                                 users.user_lastname LIKE '%gmail%' OR
                                 users.user_email LIKE '%gmail%'
 
                             ORDER BY user_date_accountcreated DESC LIMIT 0,50

J'ai bien mis un index pour les champs user_email et user_date_accountcreated. En enlevant la partie firstname et lastname, la requête est toujours aussi lente.
Elle est toujours aussi lente en enlevant le recherche sur firstname et lastname.

Est-ce qu'un interclassement utf8_swedish_ci peut affecter la vitesse d’exécution de mysql ?

Merci,
Vincent.

[Artemus24]

Salut defacta.

C'est normal que la recherche soit longue, car tu fais un "LIKE '%gmail%'".
Autrement dit, tu recherches un mot dans une chaîne de caractères et de ce fait, il va devoir balayer la totalité de la table.
Donc les index ne te servent à rien !

Tu dois revoir ton approche basée sur le "like" et sur les index qui ne sont pas adaptés à ce que tu cherches à faire.
Tu dois faire un "FULL TEXT SEARCH" : http://dev.mysql.com/doc/refman/5.7/...xt-search.html

Voici un exemple très basique :

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
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
CREATE TABLE `test`
( `id`          integer unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `texte`       text             NOT NULL
) ENGINE=myisam
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `test` (`texte`) VALUES
('the cat'),('cat the'),('these cats'),('.net'),('their cat'),('that cat'),('cats and cats'),('#5'),('dog and dogs')
--------------
 
--------------
alter table `test` add FULLTEXT KEY `ft` (`texte`)
--------------
 
--------------
select * from test
--------------
 
+----+---------------+
| id | texte         |
+----+---------------+
|  1 | the cat       |
|  2 | cat the       |
|  3 | these cats    |
|  4 | .net          |
|  5 | their cat     |
|  6 | that cat      |
|  7 | cats and cats |
|  8 | #5            |
|  9 | dog and dogs  |
+----+---------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

Et voici ce que tu peux faire comme recherche :

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
--------------
select * from test
--------------
 
+----+---------------+
| id | texte         |
+----+---------------+
|  1 | the cat       |
|  2 | cat the       |
|  3 | these cats    |
|  4 | .net          |
|  5 | their cat     |
|  6 | that cat      |
|  7 | cats and cats |
|  8 | #5            |
|  9 | dog and dogs  |
+----+---------------+
--------------
select * from test where MATCH (texte) AGAINST ('the'         IN NATURAL LANGUAGE MODE) order by id
--------------
 
+----+---------+
| id | texte   |
+----+---------+
|  1 | the cat |
|  2 | cat the |
+----+---------+
--------------
select * from test where MATCH (texte) AGAINST ('+the* +cat*' IN BOOLEAN MODE)          order by id
--------------
 
+----+------------+
| id | texte      |
+----+------------+
|  1 | the cat    |
|  2 | cat the    |
|  3 | these cats |
|  5 | their cat  |
+----+------------+
--------------
select * from test where MATCH (texte) AGAINST ('.net'        IN BOOLEAN MODE)          order by id
--------------
 
+----+-------+
| id | texte |
+----+-------+
|  4 | .net  |
+----+-------+
--------------
select * from test where MATCH (texte) AGAINST ('#5'          IN BOOLEAN MODE)          order by id
--------------
 
+----+-------+
| id | texte |
+----+-------+
|  8 | #5    |
+----+-------+
 
Appuyez sur une touche pour continuer...

@+

[defacta]

Oui je vois, c'est magique !

J'ai ajouté des index de type fulltext sur ces trois champs :

Code :

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

1
2
3
ALTER TABLE `users` ADD FULLTEXT KEY `email_fulltext` (`user_email`);
ALTER TABLE `users` ADD FULLTEXT KEY `firstname_fulltext` (`user_firstname`);
ALTER TABLE `users` ADD FULLTEXT KEY `lastname_fulltext` (`user_lastname`);

Et ma requête SQL est maintenant :

Code :

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

1
2
3
4
5
6
7
 
select * FROM users WHERE (
     user_id like '%gmail%' OR
     (MATCH (user_email) AGAINST ('gmail')) OR 
     (MATCH (user_firstname) AGAINST ('gmail')) OR 
     (MATCH (user_lastname) AGAINST ('gmail')) ) 
ORDER BY user_date_accountcreated DESC LIMIT 0,50

Celle-ci est exécutée en 0.0095 secondes.

En ajoutant le LEFT JOIN sur la table users_oauth :

Code :

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

1
2
3
4
5
6
7
 
select users.*, users_oauth.* FROM users LEFT JOIN users_oauth ON users.user_id = users_oauth.oauth_user_id WHERE (
     user_id like '%gmail%' OR
     (MATCH (user_email) AGAINST ('gmail')) OR 
     (MATCH (user_firstname) AGAINST ('gmail')) OR 
     (MATCH (user_lastname) AGAINST ('gmail')) ) 
ORDER BY user_date_accountcreated DESC LIMIT 0,50

Celle-ci est exécutée en 3.0070 secondes. Là où avant il me fallait 178 secondes. Très bien, c'est déjà beaucoup mieux

Merci pour ton aide Artemus24, je ne connaissais pas le FULLTEXT.