Discussion :

[Maljuna Kris]

Saluton,

J'hésite entre trois syntaxes pour la clause WHERE d'une requête sur une table MySQL concernant une colonne indexée (pas BINARY donc case non-sensitive) :

WHERE LEFT(col,1) = 'c'
WHERE col LIKE 'c%'
WHERE col BETWEEN 'c ' AND 'd '

Laquelle vous apparaît préférable et pourquoi ?

J'ajoute que la 3° solution me pose problème pour la lettre z, j'ai pensé à :

WHERE col BETWEEN 'z ' AND 'zzzzzz'.

Merci de m'éclairer de vos lumières et expériences.

Amike.

[pcaboche]

Le mieux, c'est surement:

WHERE col LIKE 'c%'

ou encore:

WHERE col REGEXP '^c'

REGEXP permet de traduire des expressions plus complèxes (avec des OU par exemple), mais dans ton exemple, un LIKE suffit.

[Maljuna Kris]

Merci de ta contribution à ma réflexion, pcaboche.

Cependant je doute que REGEXP soit plus performant en termes de vitesse et de consommation de ressources sur le serveur.
Les expressions régulières, très puissantes et flexibles, pêchent la plupart du temps sur ce point.
Elles constituent ce que l'on a l'habitude d'appeler "un recours au canon pour écraser une mouche".

Mon opinion est que tant LEFT que LIKE n'utilisent pas l'index de la colonne au contraire de la syntaxe avec BETWEEN, et que, pour cette raison, cette dernière syntaxe est la plus rapide, mais j'aimerais avoir des certitudes.

[Adjanakis]

Bonjour,

J'ai fait un petit test en local dont voici le resultat :

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
mysql> select benchmark(1000000000,LEFT('coucou',1)='c');
+--------------------------------------------+
| benchmark(1000000000,LEFT('coucou',1)='c') |
+--------------------------------------------+
|                                          0 |
+--------------------------------------------+
1 row in set (1 min 15.72 sec)
 
mysql> select benchmark(1000000000,'coucou' like 'c%');
+------------------------------------------+
| benchmark(1000000000,'coucou' like 'c%') |
+------------------------------------------+
|                                        0 |
+------------------------------------------+
1 row in set (50.69 sec)
 
mysql> select benchmark(1000000000,'coucou' between 'c' and 'd');
+----------------------------------------------------+
| benchmark(1000000000,'coucou' between 'c' and 'd') |
+----------------------------------------------------+
|                                                  0 |
+----------------------------------------------------+
1 row in set (1 min 27.05 sec)
 
mysql> select benchmark(1000000000,'coucou' REGEXP '^c.*');
+----------------------------------------------+
| benchmark(1000000000,'coucou' REGEXP '^c.*') |
+----------------------------------------------+
|                                            0 |
+----------------------------------------------+
1 row in set (5 min 46.77 sec)
 
mysql> select benchmark(1000000000,LEFT('coucou',1)='c');
+--------------------------------------------+
| benchmark(1000000000,LEFT('coucou',1)='c') |
+--------------------------------------------+
|                                          0 |
+--------------------------------------------+
1 row in set (1 min 16.28 sec)

Bien sur, il faudrait encore savoir si ces méthodes tiennes compte des index, mais la j'ai pas le temps de faire le test

[Maximil ian]

Mon opinion est que tant LEFT que LIKE n'utilisent pas l'index de la colonne au contraire de la syntaxe avec BETWEEN, et que, pour cette raison, cette dernière syntaxe est la plus rapide, mais j'aimerais avoir des certitudes.

Si si, left et LIKE utilsent bien des index (s'ils portent sur la partie gauche de la colonne) :

A B-tree index can be used for column comparisons in expressions that use the =, >, >=, <, <=, or BETWEEN operators. The index also can be used for LIKE comparisons if the argument to LIKE is a constant string that does not start with a wildcard character.

[pcaboche]

Cependant je doute que REGEXP soit plus performant en termes de vitesse et de consommation de ressources sur le serveur.

Ca c'est évident ! Par contre il est souvent judicieux d'avoir un code facile à lire, souple et évolutif (comme dit Knuth: "Premature optimization is the root of all evil")

Au niveau gain de performances, c'est souvent au niveau des opérations ensemblistes (les jointures) qu'il vaut mieux chercher (et faire en sorte que MySQL fasse des optimisations de lui-même en se basant sur les tailles des tables et l'existance d'index par exemple)

Maintenant, si c'est des performances pures que tu cherches, essaye avec la fonction BENCHMARK, au moins tu sera fixé !

Un exemple d'utilisation de BENCHMARK ici: http://www.developpez.net/forums/vie...ight=benchmark

EDIT:
Le temps que j'écrive ces lignes, Adjanakis a déjà fait le test. Merci Adjanakis et Maximilian !

[pcaboche]

Résultat des tests d'Adjanakis:

Sur 1000.000.000 de boucles:

- tu perds seulement 25 secondes avec un LIKE (comme quoi, on ne perd pas tant que ça à faire des choses flexibles)

- BETWEEN est moins performant que LIKE

- REGEXP est dans les choux, mais comme je disais tout à l'heure, il permet d'exprimer plus de choses (donc incontournable si l'expression devient complexe)

Encore une fois, merci Adjanakis

[Maljuna Kris]

Merci de toutes vos contributions, cela corrobore les tests que j'avais pu faire sur une petite table avec EXPLAIN SELECT sous phpMyAdmin.
LIKE l'emporte sur toutes les autres solutions.

Par contre, à ma grande surprise, si j'ajoute à la table une colonne intiale CHAR(1) indexée, et que je remplis cette colonne par UPDATE clients SET initiale=LEFT(nom,1)
La requête
SELECT nom FROM client WHERE initiale='c'
est moins rapide que
SELECT nom FROM client WHERE nom LIKE='c%', et je trouve cela très surprenant.

[Maximil ian]

Peut-être qu'un ANALYZE TABLE (pour régénérer les informations d'index) arrangerait les choses.

Sinon un EXPLAIN sur les deux SELECT nous en dirait certainement plus...