Index of MySQL database Optimization Technology

eq_ref

one row will be read from this table for each combination of rows from the previous tables. other than the const types, this is the best possible join type. it is used when all parts of an index are used by the join and the index is a Primary Key or unique index.

eq_ref can be used for indexed columns that are compared using the = operator. the comparison value can be a constant or an expression that uses columns from tables that are read before this table.

in the following examples, MySQL can use an eq_ref join to Process ref_table :

 select * from  ref_table ,  other_table  where  ref_table .  key_column  =  other_table .  column ; select * from  ref_table ,  other_table  where  ref_table .  key_column_part1  =  other_table .  column  and  ref_table .  key_column_part2  = 1; 

Ref

All rows with matching index values will be read from this table for each combination of rows from the previous tables.RefIs used if the join uses only a leftmost prefix of the key or if the key is notPrimary KeyOrUniqueIndex (in other words, if the join cannot select a single row based on the key value). If the key that is used matches only a few rows, this is a good join type.

RefCan be used for indexed columns that are compared using=Operator.

In the following examples, MySQL can useRefJoin to processRef_table:

Select * fromRef_tableWhereKey_column=Expr; Select * fromRef_table,Other_tableWhereRef_table.Key_column=Other_table.Column; Select * fromRef_table,Other_tableWhereRef_table.Key_column_part1=Other_table.ColumnAndRef_table.Key_column_part2= 1;
 

Ref_or_null