MySql > create index (‘ index ‘, ‘index’, ‘index’)

1. Where clause like uses the wildcard %keyword prefix

 select * from test_user where name like "%Kiven%";
Copy the code

2. Use >, >=, <,<=,! =,NOT IN range search or negate search, and the range of boundary search criteria selected during range search is too large

select * from test_user where height>=180# index will not be usedCopy the code

select * from test_user where height>=190# use indexCopy the code

3. Implicit data type conversion

For example, the name field is vARCHAR

select * from test_user where name=1
Copy the code

Will not be able to use indexes, while

select * from test_user where name='1'
Copy the code

You can use indexes

The reason is that MySql does a citation conversion when comparing different field types, and an int of 1 May be equal to ’01’, ‘0001’ or ’01.e1′.

4. Does not meet the leftmost matching principle (joint index)

The index order we created is

KEY `idx_name_height_weight` (`name`,`height`,`weight`)
Copy the code

So the WHERE clause cannot skip the previous syndication column when used

Select * from test_user where weight=65Copy the code

Select * from test_user where weight=65 AND name='Tom' AND 'height' =160Copy the code

6. Perform operations on index columns

select * from test_user where `height`+10=160# will not use an index, can be writtenselect * from test_user where `height`=160- 10# then you can use the indexCopy the code

MySql itself has made a lot of optimizations for us in terms of index usage. Sometimes we don’t always use indexes as we want, so we need to explore