Shulou(Shulou.com)06/01 Report--

This article originates from the assignment of "one minute to understand indexing skills".

Suppose the order business table structure is as follows:

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

Where:

Oid, order ID, key date, order date, general index, management background often query uid according to date, user ID, have general index, user query their own order status, order status, general index, management background often query money/time according to status, order amount / time, queried field, no index …

Suppose the order has three states: 0 has been placed, 1 has been paid, and 2 has been completed

Which SQL is faster to query outstanding orders for business requirements?

Select * from order where statusThe from order where status=0 2 select * from order where status=0 or status=1select * from order where status IN (0prime1) select * from order where status=0

Union all

Select * from order where status=1

Conclusion: scheme 1 is the slowest, and scheme 2, 3, 4 can hit the index.

But.

One: union all must be able to hit the index

Select * from order where status=0union allselect * from order where status=1

Description:

Directly tell MySQL what to do, MySQL consumes the least CPU

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

Two: simple in can hit the index.

Select * from order where status in (0jue 1)

Description:

Let MySQL think that query optimization costs more cpu than union all, but it can be ignored.

Programmers write SQL (in) most often, and this is the most recommended way to write this example.

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

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

Description:

Let MySQL think that query optimization consumes more cpu than in. Don't leave the burden to MySQL.

Programmers are not advised to use or frequently. Not all or hits the index.

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

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

Select * from order where statusroom2

Description:

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

Prohibit the use of negative queries

V. other options

Select * from order where status < 2

In this specific example, it's really fast, but:

In this example, there are only three states, the actual business has more than these three states, and the "value" of the state exactly satisfies the partial order relation. If you are looking for other states, SQL should not rely on the values of enumerations, and the scheme is not universal.

This SQL is not recommended for its poor readability, poor understandability and poor maintainability.

VI. 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 descselect * from order where status=0 or date

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.