Detailed analysis of or in union and index optimization in MySQL

This article originated from the homework problem of "1 Minute to Understand Index Skills".

Assume that the order business table structure is:

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

Among them:

oid, Order ID, Primary Key date, the date of placing an order, has a common index, and the management background often queries according to date uid, user ID, there is a common index, users query their own orders status, order status, common index, management background often queries according to status money/time, Order Amount/Time, Queried Field, No Index …

Assume that the order has three statuses: 0 Placed, 1 Paid, and 2 Completed

Business requirements, query outstanding 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: Scheme 1 is the slowest, and schemes 2, 3 and 4 can 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

Description:

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

Programmers don't often write SQL (union all)

2: Simple in can hit the index

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

Description:

Let MySQL think, query optimization costs cpu more than union all, but it is negligible

This is the most common way for programmers to write SQL (in). This example is the most recommended way to write

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

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

Description:

Let MySQL think, query optimization costs cpu more than in, don't give MySQL the burden

It is not recommended that programmers use or frequently. Not all or hit the index

For the old version of MySQL, it is recommended to query and analyze under

4. For! =, the negative query must not hit the index

select * from order where status!=2

Description:

Full table scanning, the lowest efficiency and the slowest of all schemes

Prohibit negative queries

5. Other programmes

select * from order where status < 2

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

This example only cites three states, the actual business is not only these three states, and the "value" of the state just meets the partial order relationship, 10,000 is to check other states, SQL should not rely on the enumerated values, and the scheme is not universal

This SQL is poorly readable, understandable, and maintainable, and is strongly disrecommended

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 an example. Don't be serious about the rationality of SQL corresponding business.

Summarize