indexing

INDEX idx_age_deptid_name ON emp(age,deptid,NAME);
Copy the code

1. Full value matching

Query fields are matched in order in the index!

The order in which SQL fields are queried has nothing to do with the order in which the fields in the index are used. The optimizer will give you automatic optimization without affecting the results of SQL execution.

2. The best left prefix rule

The difference in order between query fields and index fields can cause the index to be underused or even invalid!

Reason: With composite indexes, you need to follow the best left prefix rule, that is, if you index multiple columns, follow the left-most prefix rule. Refers to the query starting at the left front of the index and not skipping columns in the index.

Conclusion: The filtering conditions must be met in the order in which the index is created. Once a field is skipped, the fields behind the index cannot be used. Full time matches are out of order, but because they are all present, SQL automatically adjusts them

3. Do not do any calculations on index columns

Any operation on an index column (calculation, function, (automatic or manual) type conversion) will cause the index to fail and move to a full table scan

For example,

1. Use the function on the query column

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age=30;
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE LEFT(age,3)=30;
Copy the code

Conclusion: No calculation on the left side of equal sign!

2 converts the query column

create index idx_name on emp(name); 
explain select sql_no_cache * from emp where name='30000';
explain select sql_no_cache * from emp where name=30000;
Copy the code

If the string is not quoted, a conversion is done on the name column!

Conclusion: No conversion on the right side of the equal sign!

4 No range query is allowed on index columns

explain SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=5 AND emp.name = 'abcd'; 
explain SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid<=5 AND emp.name = 'abcd';
Copy the code

Suggestion: Place the index order of possible range queries last

5. Use overridden indexes whenever possible

explain SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptId=4 and name='XamgXt'; 
explain SELECT SQL_NO_CACHE age,deptId,name FROM emp WHERE emp.age=30 and deptId=4 and name='XamgXt';
Copy the code

6. Use does not equal (! = or <>)

Mysql in use does not equal (! = or <>), sometimes the index cannot be used, resulting in a full table scan.

7. Field is not NULL and is NULL

8. Fuzzy matching before and after like

9. Cut back on the OR