Shulou(Shulou.com)05/31 Report--

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.