Analyze the efficiency of or and in in mysql with examples

Preface

I encountered a website stuck problem today. After a few minutes, it Okay, I found a sql in a timing script, and the execution efficiency is very slow. The DBA suggested changing or to in, and the efficiency increased hundreds of times.

Scenario description

1. Two Table association query
2.The data volume of table1 is close to 1 million
3.The data volume of table2 is close to 9 million
4.The title field in the query condition is not indexed
5.Original query statement

SELECT a.id as id FROM `table1`as a left join table2 as b on a.id=b.id WHERE b.title="衣服" or b.title="裤子" or b.title="帽子" limit 0,100

6. Query statement after transformation

SELECT a.id as id FROM `table1`as a left join table2 as b on a.id=b.id WHERE b.title IN ("衣服","裤子","帽子") limit 0,100

Efficiency after transformation

The original SQL execution time is 5s, after the change it only takes 0.01s

Reason

After checking the information, when the amount of data exceeds one million and the conditions are not indexed, the query efficiency of or is far lower than that of in. The efficiency of or is O(n), while the efficiency of in is O (logn), when n is larger, the efficiency difference becomes more obvious.

Recommended learning: "mysql video tutorial"