Don't refer to an indexed column within an expression that must be evaluated for every row in the table. Doing so prevents use of the index. Instead, isolate the column onto one side of a comparison when possible. For example, one way to select rows containing date values from the year 1994 and up is as follows:
SELECT * FROM t WHERE YEAR(d) >= 1994;
In this case, the value of YEAR(d) must be evaluated for every row in the table, so the index cannot be used. Instead, write the query like this:
SELECT * FROM t WHERE d >= '1994-01-01';
In the rewritten expression, the indexed column stands by itself on one side of the comparison and MySQL can apply the index to optimize the query.
In situations like this, EXPLAIN is useful for verifying that one way of writing a query is better than another. For the two date-selection queries just shown, for example, you might find that EXPLAIN tells you something like this:
mysql> EXPLAIN SELECT * FROM t WHERE YEAR(d)
>= 1994\G
*********************** 1. row ***************************
table: t
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 867038
Extra: Using where
mysql> EXPLAIN SELECT * FROM t WHERE d >=
'1994-01-01'\G
*********************** 1. row ***************************
table: t
type: range
possible_keys: d
key: d
key_len: 4
ref: NULL
rows: 70968
Extra: Using where
These results indicate that the second query is indeed better from the optimizer's point of view. MySQL can perform a range scan using the index for the column d, drastically reducing the number of rows that need to be examined. (The rows value drops from 867,038 to 70,968.)
When comparing an indexed column to a value, use a value that has the same datatype as the column. For example, you can look for rows containing a numeric id value of 18 with either of the following WHERE clauses:
WHERE id = 18
WHERE id = '18'
MySQL will produce the same result either way, even though the value is specified as a number in one case and as a string in the other case. However, for the string value, MySQL must perform a string-to-number conversion, which might cause an index on the id column not to be used.
In certain cases, MySQL can use an index for pattern-matching operations performed with the LIKE operator. This is true if the pattern begins with a literal prefix value rather than with a wildcard character. An index on a name column can be used for a pattern match like this:
WHERE name LIKE 'de%'
That's because the pattern match is logically equivalent to a range search:
WHERE name >= 'de' AND name < 'df'
On the other hand, the following pattern makes LIKE more difficult for the optimizer:
WHERE name LIKE '%de%'
When a pattern starts with a wildcard character as just shown, MySQL cannot make efficient use of any indexes associated with that column. (Even if an index is used, the entire index must be scanned.)
MySQL 
/ 24 
