#emp_no   PRIMARY
#first_name
#last_name
show index from employees

#Equivalent Query=

#Range query in

#Prefix matches like'xx%'

#Inequality Query!=, >, >=, <, <=, not like, not in, like'%xx', is not null

 

Clustered index (leaf nodes hold row record data)

#const
explain select * from employees where emp_no = 10001
#range    

#Using where
explain select * from employees where emp_no !=  10001

#const
explain select * from employees where birth_date = '1953-09-02' and emp_no =  10001

#range    
#Using where
explain select * from employees where birth_date = '1953-09-02' and emp_no !=  10001

#range    
#Using where
explain select * from employees where birth_date is not null and emp_no !=  10001

#No matter how many other non-indexed columns there are, even if other columns use non-equal queries, is not NULL, not Like, not in, etc., when there is a clustered index, the related records are queried based on the primary key first, then filtered based on the conditions#

Secondary index (leaf nodes do not store row record data)

Single secondary index:

#ref
explain select * from employees where first_name =  'Parto1'

#Prefix string matching range
#Using index condition
explain select * from employees where first_name like  'Parto1%'

#Nonequivalent queries are:
#all
#Using where
explain select * from employees where first_name !=  'Parto1'
explain select * from employees where first_name like  '%Parto1'
explain select * from employees where first_name like  '%Parto1%'
explain select * from employees where first_name not like  'Parto1%'
explain select * from employees where first_name is not null

#ref
#Using where
explain select * from employees where birth_date = '1953-09-02' and first_name =  'Parto1'

#all
#Using where
explain select * from employees where birth_date = '1953-09-02' and first_name !=  'Parto1'

#A single secondary index, if it is not an equal query or a prefix string match, will be scanned in the full table, where the column is no different from the column query that is not an index. #

#When a single secondary index is combined with an aggregated index:
#1. If the aggregated index is an equivalent query, the aggregated index is preferred;
#2. If the aggregated index is an unequal query and the current secondary index is an equal query, the current secondary index is used.
#3. If the current secondary index is also an unequal query, the aggregated index is still used.

 

Multiple secondary indexes:

#index_merge(Used idx_last_name,idx_first_name)
#Using intersect(idx_last_name,idx_first_name); Using where
explain select * from employees where first_name =  'Saniya' and last_name = 'Bamford'

#range
#Using index condition; Using where; Using MRR
explain select * from employees where first_name =  'Saniya' and last_name like 'Bamfo%'

#range
#Using index condition; Using where; Using MRR
explain select * from employees where first_name like  'Sani%' and last_name like 'Bamfo%'

#index_merge
#Using intersect(idx_last_name,idx_first_name); Using where
explain select * from employees where first_name =  'Saniya' and last_name = 'Bamford' and birth_date = '1953-09-02'

#For a secondary index of a non-equivalent query, this column is no different from a non-index column query result.

#ref (Used idx_first_name)
#Using where
explain select * from employees where first_name =  'Saniya' and last_name != 'Bamford'

#all
#Using where
explain select * from employees where first_name !=  'Patricio' and last_name != 'Bamford'

#all
#Using where
explain select * from employees where first_name !=  'Patricio' and last_name is not null

#When using OR connection:
#1.1 When all columns are indexed and equal value queries, index_merge will still be used for queries
#1.2 If a column is not an index, the full table will be scanned;
#1.3 If at least one column of the index is not an equivalent query, the full table will be scanned

#index_merge
#Using union(idx_first_name,idx_last_name); Using where
explain select * from employees where first_name =  'Saniya' OR last_name = 'Bamford'

#ALL
#Using where
explain select * from employees where first_name =  'Saniya' OR last_name != 'Bamford'

#ALL
#Using where
explain select * from employees where first_name =  'Saniya' OR last_name = 'Bamford' OR birth_date = '1953-09-02'

 

 

Key Summary:

Even if an unequal query, is null, is not null, or OR is used, it cannot be said that the query must not leave the index (see the red label section).

#When using OR connection:
#1.1 When all columns are indexed and equal value queries, index_merge will still be used for queries
#1.2 If a column is not an index, the full table will be scanned;
#1.3 If at least one column of the index is not an equivalent query, the full table will be scanned

#For a secondary index of a non-equivalent query, this column is no different from a non-index column query result.

#For clustered indexes:
If the clustered index is equal matching, the clustered index must be used as long as it is an AND connection, and if it is an OR connection, it will be processed according to the OR join rules mentioned above.
If the clustered index is not an equal value match and the other columns are not index columns or auxiliary index columns for non-equal queries, the clustered index will be used.
If the clustered index is not an equivalence match and there is at least one secondary index column for the equivalence query, the secondary index will be used.