Typical scenario where MySQL has an index but cannot use an index

A like query that starts with% cannot take advantage of the B-tree index

Explain select * from actor where last_name like '%ni% ' \g;

Explain select * from actor where last_name like ' ni% ' \g;

Solutions

Scan index last_name to get a list of the primary key actor_id for%ni% that meet the criteria, and then retrieve the records based on the primary key back to the table so that access bypasses the large number of IO requests generated by the full table scan actor.

Explain select * FROM (select actor_id from actor where last_name like '%ni% ') A,actor b where a.actor_id = B.actor_id\g;

Implicit conversions for data types

Explain select * from actor where last_name=1\g;

Explain select * from actor where last_name= ' 1 ' \g;

In the case of composite indexes, the query condition does not satisfy the leftmost principle of the index

Explain select * from payment where amount=3.98 and Last_update= ' 2016-02-, 22:12:32 ' \g;

MySQL estimates that using an index is slower than a full table scan

Update Film_text set title =concat (' S ', title);

Explain select * from Film_text where title like ' s% ' \g;

It can be seen that a full table scan requires access to a record rows of 1000, the cost is calculated as 233.53;

Scan access records through the Idx_title_desc_part index rows is 998, the cost is 1198.6 higher than the full table scan time, MySQL will choose a full table scan

Conditions that are separated by or, the pre-or-post condition has an index, or the column after or is not indexed

If the condition is separated by or, the pre-or-post condition has an index, and the column after or is not indexed, then the index involved is not used

Because the condition after or does not have an index, the subsequent query is bound to perform a full table scan, in which case there is no need for more than one index scan to increase IO access in the presence of a full table scan.

Explain select * from payment where customer_id =203 or amount=3.96\g;

Typical scenario where MySQL has an index but cannot use an index