Performances de MYSQL OR vs IN

Question

Je me demande s'il existe une différence en termes de performances entre les produits suivants

SELECT ... FROM ... WHERE someFIELD IN(1,2,3,4)

SELECT ... FROM ... WHERE someFIELD between  0 AND 5

SELECT ... FROM ... WHERE someFIELD = 1 OR someFIELD = 2 OR someFIELD = 3 ... 

ou MySQL optimisera-t-il le SQL de la même manière que les compilateurs optimisent le code ?

EDIT : Changement du AND à OR pour la raison indiquée dans les commentaires.

Answer 1

Je devais en avoir le cœur net et j'ai donc testé les deux méthodes. J'ai toujours trouvé IN est beaucoup plus rapide que l'utilisation de OR .

Ne croyez pas les personnes qui donnent leur "opinion", la science est faite de tests et de preuves.

J'ai exécuté une boucle de 1000x les requêtes équivalentes (pour la cohérence, j'ai utilisé sql_no_cache ):

IN : 2.34969592094s

OR : 5.83781504631s

Mise à jour :
(Je n'ai pas le code source du test original, car il date d'il y a 6 ans, mais il renvoie un résultat dans la même fourchette que ce test).

Si je demande un exemple de code pour tester cela, voici le cas d'utilisation le plus simple possible. En utilisant Eloquent pour la simplicité de la syntaxe, l'équivalent SQL brut s'exécute de la même manière.

$t = microtime(true); 
for($i=0; $i<10000; $i++):
$q = DB::table('users')->where('id',1)
    ->orWhere('id',2)
    ->orWhere('id',3)
    ->orWhere('id',4)
    ->orWhere('id',5)
    ->orWhere('id',6)
    ->orWhere('id',7)
    ->orWhere('id',8)
    ->orWhere('id',9)
    ->orWhere('id',10)
    ->orWhere('id',11)
    ->orWhere('id',12)
    ->orWhere('id',13)
    ->orWhere('id',14)
    ->orWhere('id',15)
    ->orWhere('id',16)
    ->orWhere('id',17)
    ->orWhere('id',18)
    ->orWhere('id',19)
    ->orWhere('id',20)->get();
endfor;
$t2 = microtime(true); 
echo $t."\n".$t2."\n".($t2-$t)."\n";

1482080514.3635
1482080517.3713
3.0078368186951

$t = microtime(true); 
for($i=0; $i<10000; $i++): 
$q = DB::table('users')->whereIn('id',[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20])->get(); 
endfor; 
$t2 = microtime(true); 
echo $t."\n".$t2."\n".($t2-$t)."\n";

1482080534.0185
1482080536.178
2.1595389842987

Answer 2

J'ai aussi fait un test pour les futurs Googlers. Le nombre total de résultats retournés est de 7264 sur 10000.

SELECT * FROM item WHERE id = 1 OR id = 2 ... id = 10000

Cette requête a pris 0.1239 secondes

SELECT * FROM item WHERE id IN (1,2,3,...10000)

Cette requête a pris 0.0433 secondes

IN est 3 fois plus rapide que OR

Answer 3

Je pense que l'option ENTRE sera plus rapide puisqu'elle doit être convertie en :

Field >= 0 AND Field <= 5

Je crois savoir qu'un IN sera de toute façon converti en un ensemble d'instructions OR. L'intérêt des IN est leur facilité d'utilisation. (Il évite d'avoir à taper plusieurs fois le nom de chaque colonne et facilite également l'utilisation de la logique existante - vous n'avez pas à vous soucier de la précédence AND/OR car le IN est une seule instruction. Avec un tas d'instructions OR, vous devez veiller à les entourer de parenthèses pour vous assurer qu'elles sont évaluées comme une seule condition).

La seule vraie réponse à votre question est PROFILER VOS REQUÊTES . Vous saurez alors ce qui fonctionne le mieux dans votre situation particulière.

Answer 4

Cela dépend de ce que vous faites ; quelle est l'étendue de la plage, quel est le type de données (je sais que votre exemple utilise un type de données numérique mais votre question peut également s'appliquer à de nombreux types de données différents).

Il s'agit d'un cas où vous voulez écrire la requête dans les deux sens, la faire fonctionner et ensuite utiliser EXPLAIN pour comprendre les différences d'exécution.

Je suis sûr qu'il existe une réponse concrète à cette question, mais c'est ainsi que je trouverais, en pratique, la réponse à ma question.

Cela pourrait vous aider : http://forge.mysql.com/wiki/Top10SQLPerformanceTips

Regards,
Frank

Answer 5

Je pense qu'une explication à l'observation de sunseeker est que MySQL trie réellement les valeurs dans l'instruction IN si elles sont toutes des valeurs statiques et en utilisant la recherche binaire, qui est plus efficace que l'alternative OR simple. Je ne me souviens pas où j'ai lu cela, mais le résultat de sunseeker semble être une preuve.