Mysql optimization-in or or selection

In many cases, or or in is used to filter data when querying a database. Compare the efficiency of the two to see which is more suitable for use scenarios.

Test platform: centos7_x86_64 mysql-5.7.18

Create tables and insert test data (10 million records)

Mysql > create table t_user (id int,name varchar (30))

Query OK, 0 rows affected (0.11 sec)

Insert 10 million records through the stored procedure, the code is as follows:

Mysql > delimiter $$

Mysql > create procedure sp_insert ()

-> begin

-> declare i int

-> set I = 0

-> while i set autocommit = 0

-> set I = I + 1

-> insert into t_user values (iQuery concat ('upright Magi I))

-> if I% 5000 = 0 then

-> commit

-> end if

-> end while

-> end

-> $$

Mysql > delimiter

Mysql > call sp_insert ()

Query OK, 1 row affected (8 min 1.52 sec)

Test result

Test SQL:select from t_user where id in (… .)

Select from t_user where id =. Or id =. Or id =...

(1) the absence of an index:

Execution time of Or and in (2 records): in time 3.83s or time 3.90s

Execution time of Or and in (4 records): in 3.88s or 4.27s

Execution time of Or and in (6 records): in time 3.93s or time 4.78s

Execution time of Or and in (10 records): in 3.99s or 5.53s

(2) in the case of primay key:

Execution time of Or and in (2 records): in time 0.00061825s or time 0.00061400s

Execution time of Or and in (3 records): in time 0.00068200 or time 0.00066425

Execution time of Or and in (6 records): in time 0.00057650s or time 0.00064200s

Execution time of Or and in (10 records): in time 0.00096200s or time 0.00092925

3. Summary:

If there is an index in the column where Or or in is located. There is little difference in execution efficiency. In is more efficient when the column is not indexed. In is recommended.