|
|
|
|
| |
SELECT col3 FROM mytable
WHERE col1 = 'some value' AND col2 = 'some other value';
Suppose also that the test on col1 matches 900 rows, the test on col2 matches 300 rows, and that both tests succeed on 30 rows. Testing col1 first results in 900 rows that must be examined to find the 30 that also match the col2 value. That's 870 failed tests. Testing col2 first results in 300 rows that must be examined to find the 30 that also match the col1 value. That's only 270 failed tests, so less computation and disk I/O is required. As a result, the optimizer will attempt to test col2 first.
You can help the optimizer take advantage of indexes by using the following guidelines.
Try to compare columns that have the same type. When you use indexed columns in comparisons, use columns that are of the same type. For example, CHAR(10) is considered the same as CHAR(10) or VARCHAR(10) but different than CHAR(12) or VARCHAR(12). INT is different than BIGINT. Using columns of the same type is a requirement prior to MySQL 3.23, or indexes on the columns will not be used. From 3.23 on, this is not strictly necessary, but identical column types will still give you better performance than dissimilar types. If the columns you're comparing are of different types, you can use ALTER TABLE to modify one of them so that the types match.
Try to make indexed columns stand alone in comparison expressions. If you use a column in a function call or as part of a more complex term in an arithmetic expression, MySQL can't use the index because it must compute the value of the expression for every row. Sometimes this is unavoidable, but many times you can rewrite a query to get the indexed column to appear by itself.
|
|
|
|
|
|
| Link Partners: Asia florist, Flowers to India, Hong kong flowers, Site submit, Cheap web hosting, China florist, Japan florist |
|
