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"