Is it needed to INDEX second column for use in WHERE clause?

Question

Consider fetching data with

SELECT * FROM table WHERE column1='XX' && column2='XX'

Mysql will filter the results matching with the first part of WHERE clause, then with the second part. Am I right?

Imagine the first part match 10 records, and adding the second part filters 5 records. Is it needed to INDEX the second column too?

Answer 1

You are talking about short circuit evaluation. A DBMS has cost-based optimizer. There is no guarantee wich of both conditions will get evaluated first.

To apply that to your question: Yes, it might be benificial to index your second column.

• Is it used regulary in searches?
• What does the execution plan tell you?
• Is the access pattern going to change in the near future?
• How many records does the table contain?
• Would a Covering Index would be a better choice?
• ...

Answer 2

Indexes are optional in MySQL, but they can increase performance.

Currently, MySQL can only use one index per table select, so with the given query, if you have an index on both column1 and column2, MySQL will try to determine the index that will be the most beneficial, and use only one.

The general solution, if the speed of the select is of utmost importance, is to create a multi-column index that includes both columns.

This way, even though MySQL could only use one index for the table, it would use the multi-column index that has both columns indexed, allowing MySQL to quickly filter on both criteria in the WHERE clause.

In the multi-column index, you would put the column with the highest cardinality (the highest number of distinct values) first.

For even further optimization, "covering" indexes can be applied in some cases.

Note that indexes can increase performance, but with some cost. Indexes increase memory and storage requirements. Also, when updating or inserting records into a table, the corresponding indexes require maintenance. All of these factors must be considered when implementing indexes.

Update: MySQL 5.0 can now use an index on more than one column by merging the results from each index, with a few caveats.

The following query is a good candidate for Index Merge Optimization:

SELECT * FROM t1 WHERE key1=1 AND key2=1