How to improve the efficiency of or and in in mysql
How to improve the efficiency of or and in mysql, many novices are not very clear about this, in order to help you solve this problem, the following small series will explain in detail for everyone, there are people who need this can learn, I hope you can gain something.
preface
Today encountered a website stuck problem, after a few minutes it will be fine, found a timed script has a sql, execution efficiency is very slow, DBA suggested to change or to in, efficiency improved hundreds of times
scene description
1. Two table association queries
Table1's data volume is close to 1 million
Table2's data volume is close to 9 million
4. The title field in query criteria is not indexed
5. original query expression
SELECT a.id as id FROM `table1`as a left join table2 as b on a.id= b.id WHERE b.title="Clothing" or b.title ="Trousers" or b.title="Hat" limit 0,100
6. Reformed query statements
SELECT a.id as id FROM `table1`as a left join table2 as b on a.id= b.id WHERE b.title IN ("Clothes","Pants","Hat") limit 0,100 Efficiency after modification
The execution time of original sql is 5s, after changing it, it only takes 0.01s.
reason
The query efficiency of or is much 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 is more obvious.
Did reading the above help you? If you still want to have further understanding of related knowledge or read more related articles, please pay attention to the industry information channel, thank you for your support.
