MYSQL OR vs IN desempenho

Eu estou querendo saber se há alguma diferença em relação ao desempenho entre os seguintes

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 o MySQL irá otimizar o SQL da mesma forma que os compiladores irão otimizar o código?

EDIT: Alterou os AND ‘s para OR pela razão declarada nos comentários.

Eu precisava saber disso com certeza, então eu testei os dois methods. Eu consistentemente descobri que o IN é muito mais rápido do que usar o OR .

Não acredite em pessoas que dão sua “opinião”, a ciência é toda sobre testes e evidências.

Eu corri um loop de 1000x as consultas equivalentes (para consistência, usei sql_no_cache ):

IN : 2.34969592094s

OR : 5.83781504631s

Atualizar:
(Eu não tenho o código-fonte para o teste original, como era há 6 anos, embora ele retorne um resultado no mesmo intervalo que este teste)

Na solicitação de algum código de amostra para testar isso, aqui está o caso de uso mais simples possível. Usando o Eloquent para simplicidade de syntax, o equivalente em SQL bruto é executado da mesma forma.

 $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

Eu também fiz um teste para os futuros Googlers. Contagem total de resultados retornados é 7264 de 10000

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

Esta consulta demorou 0.1239 segundos

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

Esta consulta demorou 0.0433 segundos

IN é 3 vezes mais rápido que OR

Eu acho que o BETWEEN será mais rápido, pois deve ser convertido em:

 Field >= 0 AND Field < = 5 

No meu entender, uma IN será convertida em várias instruções OR de qualquer maneira. O valor de IN é a facilidade de uso. (Economizando em ter que digitar cada nome de coluna várias vezes e também facilita o uso com a lógica existente - você não precisa se preocupar com AND / OR precedência porque o IN é uma instrução. Com um monte de instruções OR, você tem para garantir que você os coloque entre parênteses para garantir que eles sejam avaliados como uma condição.)

A única resposta real à sua pergunta é PERFIL SUAS CONSULTAS . Então você saberá o que funciona melhor em sua situação particular.

Depende do que você está fazendo; qual é o intervalo, qual é o tipo de dados (sei que seu exemplo usa um tipo de dados numéricos, mas sua pergunta também pode ser aplicada a vários tipos de dados diferentes).

Esta é uma instância em que você deseja escrever a consulta nos dois sentidos; trabalhe e use EXPLAIN para descobrir as diferenças de execução.

Tenho certeza de que há uma resposta concreta para isso, mas é assim que eu, praticamente falando, descobriria a resposta para a minha pergunta.

Isso pode ser de alguma ajuda: http://forge.mysql.com/wiki/Top10SQLPerformanceTips

Saudações,
Frank

Eu acho que uma explicação para a observação de sunseeker é que o MySQL realmente classifica os valores na instrução IN se eles são todos valores estáticos e usam busca binária, que é mais eficiente que a alternativa OR simples. Não me lembro onde li isso, mas o resultado de sunseeker parece ser uma prova.

OR será mais lento. O fato de IN ou BETWEEN ser mais rápido dependerá dos seus dados, mas eu esperaria que o BETWEEN fosse mais rápido normalmente, já que pode simplesmente pegar um intervalo de um índice (supondo que someField esteja indexado).

Aposto que eles são iguais, você pode fazer um teste fazendo o seguinte:

Faça um loop sobre o “in (1,2,3,4)” 500 vezes e veja quanto tempo demora. Faça um loop sobre a versão “= 1 ou = 2 ou = 3 …” 500 vezes e veja quanto tempo ela é executada.

você também pode tentar um modo de junit, se someField for um índice e sua tabela for grande, pode ser mais rápido …

 SELECT ... FROM ... INNER JOIN (SELECT 1 as newField UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) dt ON someFIELD =newField 

Eu tentei o método de junit acima no meu SQL Server e é quase o mesmo que o em (1,2,3,4), e ambos resultam em uma busca de índice em cluster. Não tenho certeza de como o MySQL lidará com eles.

Abaixo estão os detalhes de 6 consultas usando o MySQL 5.6 @ SQLFiddle

Em resumo, as 6 consultas abrangem colunas indexadas independentemente e 2 consultas foram usadas por tipo de dados. Todas as consultas resultaram no uso de um índice, independentemente de IN () ou ORs sendo usados.

  | ORs | IN() integer | uses index | uses index date | uses index | uses index varchar | uses index | uses index 

Eu realmente só queria desmascarar declarações feitas que ou significa que nenhum índice pode ser usado. Isso não é verdade. Os índices podem ser usados ​​em consultas usando OR conforme as 6 consultas nos exemplos a seguir são exibidas.

Também me parece que muitos ignoraram o fato de que IN () é um atalho de syntax para um conjunto de ORs. Em desempenho de pequena escala, as diferenças entre o uso de IN () -v- OR são extremamente (infintessinalmente) marginais.

Enquanto em maior escala IN () é certamente mais conveniente, mas ainda equivale a um conjunto de condições OR logicamente. Mudança de circunstância para cada consulta, então testar sua consulta em suas tabelas é sempre melhor.

Resumo dos 6 planos de explicação, todos “Usando condição de índice” (rolagem à direita)

  Query select_type table type possible_keys key key_len ref rows filtered Extra ------------- --------- ------- --------------- ----------- --------- ----- ------ ---------- ----------------------- Integers using OR SIMPLE mytable range aNum_idx aNum_idx 4 10 100.00 Using index condition Integers using IN SIMPLE mytable range aNum_idx aNum_idx 4 10 100.00 Using index condition Dates using OR SIMPLE mytable range aDate_idx aDate_idx 6 7 100.00 Using index condition Dates using IN SIMPLE mytable range aDate_idx aDate_idx 6 7 100.00 Using index condition Varchar using OR SIMPLE mytable range aName_idx aName_idx 768 10 100.00 Using index condition Varchar using IN SIMPLE mytable range aName_idx aName_idx 768 10 100.00 Using index condition 

Violino SQL

Configuração do MySQL 5.6 Schema :

 CREATE TABLE `myTable` ( `id` mediumint(8) unsigned NOT NULL auto_increment, `aName` varchar(255) default NULL, `aDate` datetime, `aNum` mediumint(8), PRIMARY KEY (`id`) ) AUTO_INCREMENT=1; ALTER TABLE `myTable` ADD INDEX `aName_idx` (`aName`); ALTER TABLE `myTable` ADD INDEX `aDate_idx` (`aDate`); ALTER TABLE `myTable` ADD INDEX `aNum_idx` (`aNum`); INSERT INTO `myTable` (`aName`,`aDate`) VALUES ("Daniel","2017-09-19 01:22:31") ,("Quentin","2017-06-03 01:06:45") ,("Chester","2017-06-14 17:49:36") ,("Lev","2017-08-30 06:27:59") ,("Garrett","2018-10-04 02:40:37") ,("Lane","2017-01-22 17:11:21") ,("Chaim","2017-09-20 11:13:46") ,("Kieran","2018-03-10 18:37:26") ,("Cedric","2017-05-20 16:25:10") ,("Conan","2018-07-10 06:29:39") ,("Rudyard","2017-07-14 00:04:00") ,("Chadwick","2018-08-18 08:54:08") ,("Darius","2018-10-02 06:55:56") ,("Joseph","2017-06-19 13:20:33") ,("Wayne","2017-04-02 23:20:25") ,("Hall","2017-10-13 00:17:24") ,("Craig","2016-12-04 08:15:22") ,("Keane","2018-03-12 04:21:46") ,("Russell","2017-07-14 17:21:58") ,("Seth","2018-07-25 05:51:30") ,("Cole","2018-06-09 15:32:53") ,("Donovan","2017-08-12 05:21:35") ,("Damon","2017-06-27 03:44:19") ,("Brian","2017-02-01 23:35:20") ,("Harper","2017-08-25 04:29:27") ,("Chandler","2017-09-30 23:54:06") ,("Edward","2018-07-30 12:18:07") ,("Curran","2018-05-23 09:31:53") ,("Uriel","2017-05-08 03:31:43") ,("Honorato","2018-04-07 14:57:53") ,("Griffin","2017-01-07 23:35:31") ,("Hasad","2017-05-15 05:32:41") ,("Burke","2017-07-04 01:11:19") ,("Hyatt","2017-03-14 17:12:28") ,("Brenden","2017-10-17 05:16:14") ,("Ryan","2018-10-10 08:07:55") ,("Giacomo","2018-10-06 14:21:21") ,("James","2018-02-06 02:45:59") ,("Colt","2017-10-10 08:11:26") ,("Kermit","2017-09-18 16:57:16") ,("Drake","2018-05-20 22:08:36") ,("Berk","2017-04-16 17:39:32") ,("Alan","2018-09-01 05:33:05") ,("Deacon","2017-04-20 07:03:05") ,("Omar","2018-03-02 15:04:32") ,("Thaddeus","2017-09-19 04:07:54") ,("Troy","2016-12-13 04:24:08") ,("Rogan","2017-11-02 00:03:25") ,("Grant","2017-08-21 01:45:16") ,("Walker","2016-11-26 15:54:52") ,("Clarke","2017-07-20 02:26:56") ,("Clayton","2018-08-16 05:09:29") ,("Denton","2018-08-11 05:26:05") ,("Nicholas","2018-07-19 09:29:55") ,("Hashim","2018-08-10 20:38:06") ,("Todd","2016-10-25 01:01:36") ,("Xenos","2017-05-11 22:50:35") ,("Bert","2017-06-17 18:08:21") ,("Oleg","2018-01-03 13:10:32") ,("Hall","2018-06-04 01:53:45") ,("Evan","2017-01-16 01:04:25") ,("Mohammad","2016-11-18 05:42:52") ,("Armand","2016-12-18 06:57:57") ,("Kaseem","2018-06-12 23:09:57") ,("Colin","2017-06-29 05:25:52") ,("Arthur","2016-12-29 04:38:13") ,("Xander","2016-11-14 19:35:32") ,("Dante","2016-12-01 09:01:04") ,("Zahir","2018-02-17 14:44:53") ,("Raymond","2017-03-09 05:33:06") ,("Giacomo","2017-04-17 06:12:52") ,("Fulton","2017-06-04 00:41:57") ,("Chase","2018-01-14 03:03:57") ,("William","2017-05-08 09:44:59") ,("Fuller","2017-03-31 20:35:20") ,("Jarrod","2017-02-15 02:45:29") ,("Nissim","2018-03-11 14:19:25") ,("Chester","2017-11-05 00:14:27") ,("Perry","2017-12-24 11:58:04") ,("Theodore","2017-06-26 12:34:12") ,("Mason","2017-10-02 03:53:49") ,("Brenden","2018-10-08 10:09:47") ,("Jerome","2017-11-05 20:34:25") ,("Keaton","2018-08-18 00:55:56") ,("Tiger","2017-05-21 16:59:07") ,("Benjamin","2018-04-10 14:46:36") ,("John","2018-09-05 18:53:03") ,("Jakeem","2018-10-11 00:17:38") ,("Kenyon","2017-12-18 22:19:29") ,("Ferris","2017-03-29 06:59:13") ,("Hoyt","2017-01-03 03:48:56") ,("Fitzgerald","2017-07-27 11:27:52") ,("Forrest","2017-10-05 23:14:21") ,("Jordan","2017-01-11 03:48:09") ,("Lev","2017-05-25 08:03:39") ,("Chase","2017-06-18 19:09:23") ,("Ryder","2016-12-13 12:50:50") ,("Malik","2017-11-19 15:15:55") ,("Zeph","2018-04-04 11:22:12") ,("Amala","2017-01-29 07:52:17") ; 

.

 update MyTable set aNum = id ; 

Consulta 1 :

 select 'aNum by OR' q, mytable.* from mytable where aNum = 12 OR aNum = 22 OR aNum = 27 OR aNum = 32 OR aNum = 42 OR aNum = 52 OR aNum = 62 OR aNum = 65 OR aNum = 72 OR aNum = 82 

Resultados :

 | q | id | aName | aDate | aNum | |------------|----|----------|----------------------|------| | aNum by OR | 12 | Chadwick | 2018-08-18T08:54:08Z | 12 | | aNum by OR | 22 | Donovan | 2017-08-12T05:21:35Z | 22 | | aNum by OR | 27 | Edward | 2018-07-30T12:18:07Z | 27 | | aNum by OR | 32 | Hasad | 2017-05-15T05:32:41Z | 32 | | aNum by OR | 42 | Berk | 2017-04-16T17:39:32Z | 42 | | aNum by OR | 52 | Clayton | 2018-08-16T05:09:29Z | 52 | | aNum by OR | 62 | Mohammad | 2016-11-18T05:42:52Z | 62 | | aNum by OR | 65 | Colin | 2017-06-29T05:25:52Z | 65 | | aNum by OR | 72 | Fulton | 2017-06-04T00:41:57Z | 72 | | aNum by OR | 82 | Brenden | 2018-10-08T10:09:47Z | 82 | 

Consulta 2 :

 select 'aNum by IN' q, mytable.* from mytable where aNum IN ( 12 , 22 , 27 , 32 , 42 , 52 , 62 , 65 , 72 , 82 ) 

Resultados :

 | q | id | aName | aDate | aNum | |------------|----|----------|----------------------|------| | aNum by IN | 12 | Chadwick | 2018-08-18T08:54:08Z | 12 | | aNum by IN | 22 | Donovan | 2017-08-12T05:21:35Z | 22 | | aNum by IN | 27 | Edward | 2018-07-30T12:18:07Z | 27 | | aNum by IN | 32 | Hasad | 2017-05-15T05:32:41Z | 32 | | aNum by IN | 42 | Berk | 2017-04-16T17:39:32Z | 42 | | aNum by IN | 52 | Clayton | 2018-08-16T05:09:29Z | 52 | | aNum by IN | 62 | Mohammad | 2016-11-18T05:42:52Z | 62 | | aNum by IN | 65 | Colin | 2017-06-29T05:25:52Z | 65 | | aNum by IN | 72 | Fulton | 2017-06-04T00:41:57Z | 72 | | aNum by IN | 82 | Brenden | 2018-10-08T10:09:47Z | 82 | 

Consulta 3 :

 select 'adate by OR' q, mytable.* from mytable where aDate= str_to_date("2017-02-15 02:45:29",'%Y-%m-%d %h:%i:%s') OR aDate = str_to_date("2018-03-10 18:37:26",'%Y-%m-%d %h:%i:%s') OR aDate = str_to_date("2017-05-20 16:25:10",'%Y-%m-%d %h:%i:%s') OR aDate = str_to_date("2018-07-10 06:29:39",'%Y-%m-%d %h:%i:%s') OR aDate = str_to_date("2017-07-14 00:04:00",'%Y-%m-%d %h:%i:%s') OR aDate = str_to_date("2018-08-18 08:54:08",'%Y-%m-%d %h:%i:%s') OR aDate = str_to_date("2018-10-02 06:55:56",'%Y-%m-%d %h:%i:%s') OR aDate = str_to_date("2017-04-20 07:03:05",'%Y-%m-%d %h:%i:%s') OR aDate = str_to_date("2018-03-02 15:04:32",'%Y-%m-%d %h:%i:%s') OR aDate = str_to_date("2017-09-19 04:07:54",'%Y-%m-%d %h:%i:%s') OR aDate = str_to_date("2016-12-13 04:24:08",'%Y-%m-%d %h:%i:%s') 

Resultados :

 | q | id | aName | aDate | aNum | |-------------|----|----------|----------------------|------| | adate by OR | 47 | Troy | 2016-12-13T04:24:08Z | 47 | | adate by OR | 76 | Jarrod | 2017-02-15T02:45:29Z | 76 | | adate by OR | 44 | Deacon | 2017-04-20T07:03:05Z | 44 | | adate by OR | 46 | Thaddeus | 2017-09-19T04:07:54Z | 46 | | adate by OR | 10 | Conan | 2018-07-10T06:29:39Z | 10 | | adate by OR | 12 | Chadwick | 2018-08-18T08:54:08Z | 12 | | adate by OR | 13 | Darius | 2018-10-02T06:55:56Z | 13 | 

Consulta 4 :

 select 'adate by IN' q, mytable.* from mytable where aDate IN ( str_to_date("2017-02-15 02:45:29",'%Y-%m-%d %h:%i:%s') , str_to_date("2018-03-10 18:37:26",'%Y-%m-%d %h:%i:%s') , str_to_date("2017-05-20 16:25:10",'%Y-%m-%d %h:%i:%s') , str_to_date("2018-07-10 06:29:39",'%Y-%m-%d %h:%i:%s') , str_to_date("2017-07-14 00:04:00",'%Y-%m-%d %h:%i:%s') , str_to_date("2018-08-18 08:54:08",'%Y-%m-%d %h:%i:%s') , str_to_date("2018-10-02 06:55:56",'%Y-%m-%d %h:%i:%s') , str_to_date("2017-04-20 07:03:05",'%Y-%m-%d %h:%i:%s') , str_to_date("2018-03-02 15:04:32",'%Y-%m-%d %h:%i:%s') , str_to_date("2017-09-19 04:07:54",'%Y-%m-%d %h:%i:%s') , str_to_date("2016-12-13 04:24:08",'%Y-%m-%d %h:%i:%s') ) 

Resultados :

 | q | id | aName | aDate | aNum | |-------------|----|----------|----------------------|------| | adate by IN | 47 | Troy | 2016-12-13T04:24:08Z | 47 | | adate by IN | 76 | Jarrod | 2017-02-15T02:45:29Z | 76 | | adate by IN | 44 | Deacon | 2017-04-20T07:03:05Z | 44 | | adate by IN | 46 | Thaddeus | 2017-09-19T04:07:54Z | 46 | | adate by IN | 10 | Conan | 2018-07-10T06:29:39Z | 10 | | adate by IN | 12 | Chadwick | 2018-08-18T08:54:08Z | 12 | | adate by IN | 13 | Darius | 2018-10-02T06:55:56Z | 13 | 

Consulta 5 :

 select 'name by OR' q, mytable.* from mytable where aname = 'Alan' OR aname = 'Brian' OR aname = 'Chandler' OR aname = 'Darius' OR aname = 'Evan' OR aname = 'Ferris' OR aname = 'Giacomo' OR aname = 'Hall' OR aname = 'James' OR aname = 'Jarrod' 

Resultados :

 | q | id | aName | aDate | aNum | |-------------|----|----------|----------------------|------| | name by OR | 43 | Alan | 2018-09-01T05:33:05Z | 43 | | name by OR | 24 | Brian | 2017-02-01T23:35:20Z | 24 | | name by OR | 26 | Chandler | 2017-09-30T23:54:06Z | 26 | | name by OR | 13 | Darius | 2018-10-02T06:55:56Z | 13 | | name by OR | 61 | Evan | 2017-01-16T01:04:25Z | 61 | | name by OR | 90 | Ferris | 2017-03-29T06:59:13Z | 90 | | name by OR | 37 | Giacomo | 2018-10-06T14:21:21Z | 37 | | name by OR | 71 | Giacomo | 2017-04-17T06:12:52Z | 71 | | name by OR | 16 | Hall | 2017-10-13T00:17:24Z | 16 | | name by OR | 60 | Hall | 2018-06-04T01:53:45Z | 60 | | name by OR | 38 | James | 2018-02-06T02:45:59Z | 38 | | name by OR | 76 | Jarrod | 2017-02-15T02:45:29Z | 76 | 

Consulta 6 :

 select 'name by IN' q, mytable.* from mytable where aname IN ( 'Alan' ,'Brian' ,'Chandler' , 'Darius' , 'Evan' , 'Ferris' , 'Giacomo' , 'Hall' , 'James' , 'Jarrod' ) 

Resultados :

 | q | id | aName | aDate | aNum | |------------|----|----------|----------------------|------| | name by IN | 43 | Alan | 2018-09-01T05:33:05Z | 43 | | name by IN | 24 | Brian | 2017-02-01T23:35:20Z | 24 | | name by IN | 26 | Chandler | 2017-09-30T23:54:06Z | 26 | | name by IN | 13 | Darius | 2018-10-02T06:55:56Z | 13 | | name by IN | 61 | Evan | 2017-01-16T01:04:25Z | 61 | | name by IN | 90 | Ferris | 2017-03-29T06:59:13Z | 90 | | name by IN | 37 | Giacomo | 2018-10-06T14:21:21Z | 37 | | name by IN | 71 | Giacomo | 2017-04-17T06:12:52Z | 71 | | name by IN | 16 | Hall | 2017-10-13T00:17:24Z | 16 | | name by IN | 60 | Hall | 2018-06-04T01:53:45Z | 60 | | name by IN | 38 | James | 2018-02-06T02:45:59Z | 38 | | name by IN | 76 | Jarrod | 2017-02-15T02:45:29Z | 76 | 

Apenas quando você pensou que era seguro …

Qual é o seu valor de eq_range_index_dive_limit ? Em particular, você tem mais ou menos itens na cláusula IN ?

Isso não includeá um Benchmark, mas examinará um pouco o funcionamento interno. Vamos usar uma ferramenta para ver o que está acontecendo – o Optimizer Trace.

A consulta: SELECT * FROM canada WHERE id ...

Com um OR de 3 valores , parte do rastreio é semelhante a:

  "condition_processing": { "condition": "WHERE", "original_condition": "((`canada`.`id` = 296172) or (`canada`.`id` = 295093) or (`canada`.`id` = 293626))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(multiple equal(296172, `canada`.`id`) or multiple equal(295093, `canada`.`id`) or multiple equal(293626, `canada`.`id`))" }, 

  "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "id", "ranges": [ "293626 < = id <= 293626", "295093 <= id <= 295093", "296172 <= id <= 296172" ], "index_dives_for_eq_ranges": true, "chosen": true 

...

  "refine_plan": [ { "table": "`canada`", "pushed_index_condition": "((`canada`.`id` = 296172) or (`canada`.`id` = 295093) or (`canada`.`id` = 293626))", "table_condition_attached": null, "access_type": "range" } ] 

Observe como o ICP está recebendo ORs . Isto implica que o OR não é transformado em IN , e o InnoDB irá realizar um monte de testes através do ICP. (Eu não sinto que vale a pena considerar MyISAM.)

(Este é o 5.6.22-71.0-log do Percona; id é um índice secundário.)

Agora para IN () com alguns valores

eq_range_index_dive_limit = 10; Existem 8 valores.

  "condition_processing": { "condition": "WHERE", "original_condition": "(`canada`.`id` in (296172,295093,293626,295573,297148,296127,295588,295810))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(`canada`.`id` in (296172,295093,293626,295573,297148,296127,295588,295810))" }, 

...

  "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "id", "ranges": [ "293626 < = id <= 293626", "295093 <= id <= 295093", "295573 <= id <= 295573", "295588 <= id <= 295588", "295810 <= id <= 295810", "296127 <= id <= 296127", "296172 <= id <= 296172", "297148 <= id <= 297148" ], "index_dives_for_eq_ranges": true, "chosen": true 

...

  "refine_plan": [ { "table": "`canada`", "pushed_index_condition": "(`canada`.`id` in (296172,295093,293626,295573,297148,296127,295588,295810))", "table_condition_attached": null, "access_type": "range" } ] 

Observe que o IN não parece estar transformado em OR .

Uma nota lateral: observe que os valores constantes foram classificados . Isso pode ser benéfico de duas maneiras:

  • Ao pular menos, pode haver melhor cache, menos E / S para obter todos os valores.
  • Se duas consultas semelhantes vierem de conexões separadas e estiverem em transactions, haverá uma chance maior de obter um atraso em vez de um impasse devido a listas sobrepostas.

Finalmente, IN () com muitos valores

  { "condition_processing": { "condition": "WHERE", "original_condition": "(`canada`.`id` in (293831,292259,292881,293440,292558,295792,292293,292593,294337,295430,295034,297060,293811,295587,294651,295559,293213,295742,292605,296018,294529,296711,293919,294732,294689,295540,293000,296916,294433,297112,293815,292522,296816,293320,293232,295369,291894,293700,291839,293049,292738,294895,294473,294023,294173,293019,291976,294923,294797,296958,294075,293450,296952,297185,295351,295736,296312,294330,292717,294638,294713,297176,295896,295137,296573,292236,294966,296642,296073,295903,293057,294628,292639,293803,294470,295353,297196,291752,296118,296964,296185,295338,295956,296064,295039,297201,297136,295206,295986,292172,294803,294480,294706,296975,296604,294493,293181,292526,293354,292374,292344,293744,294165,295082,296203,291918,295211,294289,294877,293120,295387))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(`canada`.`id` in (293831,292259,292881,293440,292558,295792,292293,292593,294337,295430,295034,297060,293811,295587,294651,295559,293213,295742,292605,296018,294529,296711,293919,294732,294689,295540,293000,296916,294433,297112,293815,292522,296816,293320,293232,295369,291894,293700,291839,293049,292738,294895,294473,294023,294173,293019,291976,294923,294797,296958,294075,293450,296952,297185,295351,295736,296312,294330,292717,294638,294713,297176,295896,295137,296573,292236,294966,296642,296073,295903,293057,294628,292639,293803,294470,295353,297196,291752,296118,296964,296185,295338,295956,296064,295039,297201,297136,295206,295986,292172,294803,294480,294706,296975,296604,294493,293181,292526,293354,292374,292344,293744,294165,295082,296203,291918,295211,294289,294877,293120,295387))" }, 

...

  "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "id", "ranges": [ "291752 < = id <= 291752", "291839 <= id <= 291839", ... "297196 <= id <= 297196", "297201 <= id <= 297201" ], "index_dives_for_eq_ranges": false, "rows": 111, "chosen": true 

...

  "refine_plan": [ { "table": "`canada`", "pushed_index_condition": "(`canada`.`id` in (293831,292259,292881,293440,292558,295792,292293,292593,294337,295430,295034,297060,293811,295587,294651,295559,293213,295742,292605,296018,294529,296711,293919,294732,294689,295540,293000,296916,294433,297112,293815,292522,296816,293320,293232,295369,291894,293700,291839,293049,292738,294895,294473,294023,294173,293019,291976,294923,294797,296958,294075,293450,296952,297185,295351,295736,296312,294330,292717,294638,294713,297176,295896,295137,296573,292236,294966,296642,296073,295903,293057,294628,292639,293803,294470,295353,297196,291752,296118,296964,296185,295338,295956,296064,295039,297201,297136,295206,295986,292172,294803,294480,294706,296975,296604,294493,293181,292526,293354,292374,292344,293744,294165,295082,296203,291918,295211,294289,294877,293120,295387))", "table_condition_attached": null, "access_type": "range" } ] 

Nota lateral: Eu precisava disso devido ao volume do traço:

 @@global.optimizer_trace_max_mem_size = 32222; 

Pelo que entendi sobre a maneira como o compilador otimiza esses tipos de consultas, usar a cláusula IN é mais eficiente do que várias cláusulas OR. Se você tem valores onde a cláusula BETWEEN pode ser usada, isso é mais eficiente ainda.

Eu sei que, contanto que você tenha um índice no Field, o BETWEEN irá usá-lo para encontrar rapidamente uma extremidade e, em seguida, atravessar a outra. Isso é mais eficiente.

Cada EXPLAIN que vi mostra “IN (…)” e “… OR …” para ser intercambiável e igualmente (in) eficiente. O que você esperaria, já que o otimizador não tem como saber se eles compreendem ou não um intervalo. Também é equivalente a UNION ALL SELECT nos valores individuais.

2018: IN é mais rápido. Mas > = && < = é ainda mais rápido que IN .

Aqui está meu benchmark .

A resposta aceita não explica o motivo.

Abaixo estão citados o MySQL de Alto Desempenho, 3ª Edição.

Em muitos servidores de database, IN () é apenas um sinônimo para várias cláusulas OR, porque os dois são logicamente equivalentes. Não é assim no MySQL, que classifica os valores na lista IN () e usa uma busca binária rápida para ver se um valor está na lista. Este é O (Log n) no tamanho da lista, enquanto uma série equivalente de cláusulas OR é O (n) no tamanho da lista (isto é, muito mais lenta para listas grandes)