Mysql Question posted in
The official MySQL documentation can be found here.

how mysql process select with many value in IN clause

For sql like select * from table where fieldA in (...), if there are lots of values in parentheses(like 1000 values), and there is a B-tree index on fieldA. How mysql execute this sql.

Does it work like foreach(value in values) {select * from table where fieldA=value}, or it has some better way to do it

In this question Is SQL IN bad for performance?. It only says IN predicate is translated into OR predicate. It doesn’t tell me what is the execution plan.

Tags:

2

Answers


  1. 0

    It depends on the size of the table and values in it. It could be a "full table scan", usually when the number of selected values is rather close to the size of the table or the number of values is very high. Or the index could be used. It really depends on the production data within the table.

    Login or Signup to reply.
  2. 0

    See next experiment.
    1.Create table (about 1250 rows) – significant for test.With this size Index seek better then full table scan
    2.Run query with IN(…) without{"query_cost": "125.35"} and with index {"query_cost": "19.41"}.
    3.See difference. Query with index is faster than without.
    Will the DBMS deploy the condition IN(…) in the sequence (… or … or …), or use the index and JOIN, or some other optimization, depends significantly on the availability of indexes and other properties of the database.

    create table test (id int ,code varchar(254),description char(200)); 
-- create index ix_code on test (code);
create index ix_id on test (id);
insert into test(id,code,description) values
 (1,'C1','Description1')
 ;
 set @@cte_max_recursion_depth=2000;
 insert into test (id,code,description)
with recursive r as(
  select 1 i,1 id, cast(concat('c',1) as char(50)) code
     ,concat('Description',cast(1 as char(100))) Description 
  from test
  union all
  select i+1 i,cast(round(rand()*1000,0) as signed) id
     ,cast(concat('c',cast(cast(round(rand()*1000,0) as signed) as char(30)))as char(50)) code
     ,cast(concat('Description',i) as char(100)) Description 
  from r
  where i<1250
)
select id,code,description from r;

EXPLAIN FORMAT=JSON 
select * from test
where id in(11,12,13,14,15,16,17,18,19,20,1,2,3,4,5,6,7,8,9,10)
;

    Test query

    select * from test
where id in(11,12,13,14,15,16,17,18,19,20,1,2,3,4,5,6,7,8,9,10);

    Some data about query without index

    {
"query_block": {
"select_id": 1,
"cost_info": {"query_cost": "125.35"},
"table": {
"table_name": "test",
"access_type": "ALL",
"rows_examined_per_scan": 1251,
"rows_produced_per_join": 625,
"filtered": "50.00",
"cost_info": {
"read_cost": "62.80",
"eval_cost": "62.55",
"prefix_cost": "125.35",
"data_read_per_join": "1M"
},
"used_columns": ["id","code","description"],
"attached_condition": "(`sandbox_db`.`test`.`id` in (11,12,13,14,15,16,17,18,19,20,1,2,3,4,5,6,7,8,9,10))"
}

    With index

    {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "19.41"
},
"table": {
"table_name": "test",
"access_type": "range",
"possible_keys": ["ix_id"],
"key": "ix_id",
"used_key_parts": ["id"],
"key_length": "5",
"rows_examined_per_scan": 32,
"rows_produced_per_join": 32,
"filtered": "100.00",
"index_condition": "(`sandbox_db`.`test`.`id` in (11,12,13,14,15,16,17,18,19,20,1,2,3,4,5,6,7,8,9,10))",
"cost_info": {
"read_cost": "16.21",
"eval_cost": "3.20",
"prefix_cost": "19.41",
"data_read_per_join": "57K"
},
"used_columns": ["id","code","description"]
}

    Fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search