In a database query, there are many cases where the or or in is used to filter the data. Here, compare the efficiency of the two to see which is more suitable for the use of the scene.
Test platform: centos7_x86_64 mysql-5.7.18
CREATE TABLE, 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, with the following code:
Mysql> delimiter $$
Mysql> CREATE PROCEDURE Sp_insert ()
Begin
-Declare i int;
Set i = 0;
-And while I-<= 10000000 do
Set autocommit = 0;
Set i = i + 1;
INSERT into T_user values (I,concat (' U ', i))
If i%5000 = 0 Then
commit;
-End If;
and end while;
-End
$$
Mysql> delimiter;
Mysql> call Sp_insert ();
Query OK, 1 row affected (8 min 1.52 sec)
- Test results
Test Sql:select from T_user where ID in (...);
Select from t_user where id =. or id =: or id = ...
(1) Non-indexed case:
Execution time of OR and in (2 Records): In Spents 3.83s or when 3.90s
Execution time of OR and in (4 Records): In Spents 3.88s or when 4.27s
Execution time of OR and in (6 Records): In Spents 3.93s or when 4.78s
Execution time of OR and in (10 Records): In Spents 3.99s or when 5.53s
(2) is the case of Primay key:
Execution time of OR and in (2 Records): In Spents 0.00061825s or when 0.00061400s
Execution time of OR and in (3 Records): In spents 0.00068200 or spents 0.00066425
Execution time of OR and in (6 Records): In Spents 0.00057650s or when 0.00064200s
Execution time of OR and in (10 Records): In spents 0.00096200s or spents 0.00092925
3. Summary:
If or or in where the column is indexed. There is little difference in execution efficiency. In the case where the column is not indexed, in is more efficient. Recommended in.
MySQL optimized--in or or selection