Where col1 = val1 and col2 = val2; index exists on col1 and col2, the appropriate rows can be fetched dir

Http://www.jianblog.com/2006/07/10/70/

Suppose that you issue the followingSelectStatement:

 
Mysql>Select * fromTbl_nameWhere col1 =Val1And col2 =Val2;
 

 

If a multiple-column index exists on col1 and col2 , the appropriate rows can be fetched directly. if separate single-column indexes exist on col1 and col2 , the optimizer tries to find the most restrictive index by deciding which index finds fewer rows and using that index to fetch the rows.

If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to find rows. for example, if you have a three-column index on (col1, col2, col3) , you have Indexed search capabilities on (col1) , (col1, col2) , and (col1, col2, col3) .

MySQL cannot use a partial index if the columns do not form a leftmost prefix of the index. Suppose that you haveSelectStatements shown here:

 
Select * fromTbl_nameWhere col1 =Val1;
 

 
Select * fromTbl_nameWhere col1 =Val1And col2 =Val2;
 

Select * fromTbl_nameWhere col2 =Val2;
 

 
Select * fromTbl_nameWhere col2 =Val2And col3 =Val3;
 

 

If an index exists on(Col1, col2, col3), Only the first two queries use the index. The third and fourth queries do involve indexed columns,(Col2)And(Col2, col3)Are not leftmost prefixes(Col1, col2, col3).

A B-tree index can be used for column comparisons in expressions that use = ,> , > = , < , <= , Or Between Operators. The index also can be used Like Comparisons if the argumentLike Is a constant string that does not start with a wildcard character. For example, the following Select Statements use indexes:

Select * fromTbl_nameWhereKey_colLike 'Patrick % '; select * fromTbl_nameWhereKey_colLike 'pat % _ ck % ';
 

In the first statement, only rows'Patrick '<=<'Pattern' are considered. In the second statement, only rows with <'pau' are considered.The followingSelectStatements do not use indexes:StringIs longer than three characters, MySQL usesTurbo Boyer-Moore algorithmTo initialize the pattern for the string and then uses this pattern to perform the search more quickly. Key_col <'Pattern' are considered. In the second statement, only rows 'Pat '<=Key_col<'Pau' are considered.<'Pau' are considered.

 
Select * fromTbl_nameWhereKey_colLike '% Patrick %'; select * fromTbl_nameWhereKey_colLikeOther_col;
 

In the first statement,LikeValue begins with a wildcard character. In the second statement,LikeValue is not a constant.

MySQL 4.0 and later versions perform an additionalLikeOptimization. If you use... Like '%String%'And

A search usingCol_nameIs nullEmploys indexes ifCol_nameIs indexed.

Any index that does not span allAndLevels inWhereClause is not used to optimize the query. In other words, to be able to use an index, a prefix of the index must be used in everyAndGroup.

The followingWhereClauses use indexes:

... Where Index_part1 = 1 and Index_part2 = 2 and Other_column = 3 /*Index = 1 or Index = 2 */... where Index = 1 or a = 10 and Index = 2/* optimized like" Index_part1 = 'Hello' "*/... where Index_part1 = 'Hello' and Index_part3 = 5/* can use index onIndex1 But not on Index2 Or Index3 */... Where Index1 = 1 and Index2 = 2 or Index1 = 3 and Index3 = 3;

TheseWhereKrases doNotUse indexes:

 
/*Index_part1Is not used */... whereIndex_part2= 1 andIndex_part3= 2/* index is not used in both parts of the WHERE clause */... whereIndex= 1 or a = 10/* No index spans all rows */... whereIndex_part1= 1 orIndex_part2= 10
 

Sometimes MySQL does not use an index, even if one is available. one circumstance under which this occurs is when the optimizer estimates that using the index wowould require MySQL to access a very large percentage of the rows in the table. (in this case, a table scan is likely to be much faster because it requires fewer seeks .) however, if such a query usesLimitTo retrieve only some of the rows, MySQL uses an index anyway, because it can much more quickly find the few rows to return in the result.

Hash indexes have somewhat different characteristics from those just discussed:

• They are used only for Equality comparisons that use=Or<=>Operators (but areVeryFast). They are not used for comparison operators such<That find a range of values.
• The optimizer cannot use a hash index to speed upOrderOperations. (This type of index cannot be used to search for the next entry in order .)
• MySQL cannot determine approximately how many rows there are between two values (this is used by the range optimizer to decide which index to use). This may affect some queries if you changeMyISAMTable to a hash-indexedMemoryTable.
• Only whole keys can be used to search for a row. (with a B-tree index, any leftmost prefix of the key can be used to find rows .)