Detailed Analysis of or, in, union and Index Optimization in MySQL

This article originated from the homework assignment of "Learn Indexing Skills in One Minute".

Assume that the order business table structure is:

order(oid, date, uid, status, money, time, …)

in:

• oid, order ID, primary key
• date, order date, has a common index, and the management backend often queries by date
• uid, user ID, has a common index, users query their own orders
• Status, order status, has a common index, and the management backend often queries according to status
• money/time, order amount/time, queried field, no index
• …

Assume that an order has three states: 0 has been placed, 1 has been paid, and 2 has been completed.

Business requirement: query unfinished orders, which SQL is faster?

• select * from order where status!=2
• select * from order where status=0 or status=1
• select * from order where status IN (0,1)
• select * from order where status=0
  union all
  select * from order where status=1

Conclusion: Solution 1 is the slowest, while solutions 2, 3, and 4 can all hit the index.

but...

1: union all can definitely hit the index

select * from order where status=0

union all

select * from order where status=1

illustrate:

Tell MySQL what to do directly, MySQL consumes the least CPU

Programmers don't often write SQL like this (union all)

2: Simple in can hit the index

select * from order where status in (0,1)

illustrate:

Let MySQL think, query optimization consumes more CPU than union all, but it is negligible

Programmers often write SQL (in) like this. In this example, it is recommended to write it like this

Three: For or, the new version of MySQL can hit the index

select * from order where status=0 or status=1

illustrate:

Let MySQL think. Query optimization consumes more CPU than IN. Don't put the burden on MySQL.

It is not recommended that programmers use or frequently, as not all ors will hit the index.

For older versions of MySQL, it is recommended to query and analyze

4. For !=, negative queries will definitely not hit the index

select * from order where status!=2

illustrate:

Full table scan, the least efficient and slowest of all solutions

Negative lookups are prohibited

V. Other options

select * from order where status < 2

In this specific example, it is indeed fast, but:

This example only gives three states. The actual business has more than these three states, and the "value" of the state just satisfies the partial order relationship. What if you want to check other states? SQL should not rely on the value of the enumeration, and the solution is not universal.

This SQL has poor readability, poor understandability, and poor maintainability. It is strongly not recommended.

6. Homework

Can such a query hit the index?

select * from order where uid in (

   select uid from order where status=0

)

select * from order where status in (0, 1) order by date desc

select * from order where status=0 or date <= CURDATE()

Note: This is just an example. Don’t be too picky about the rationality of the SQL corresponding to the business.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM.