Do I need to use the second INDEX column to use in the WHERE clause?

Question

Consider fetching data using

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

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

Imagine that the first part corresponds to 10 records and adds the second part of the filters to 5 records. Does INDEX need a second column?

Answer 1

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

Currently, MySQL can use only one index to select a table, so with this query, if you have an index for columns1 and column2, MySQL will try to determine the index that will be most useful and use only one.

The general solution, if speed of choice is paramount, is to create an index with multiple columns that includes both columns.

Thus, while MySQL can only use one index per table, it will use a multi-column index that is indexed by both columns, which allows MySQL to quickly filter both criteria in the WHERE clause.

In an index with multiple columns, you must first put the column with the highest power (the largest number of different values).

For even greater optimization, in some cases, covering indexes can be used.

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

Update: MySQL 5.0 can now use an index in more than one column, combining the results of each index with several caveats.

The following query is a good candidate for optimizing index merging :

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

Answer 2

You are talking about short circuit assessment . The DBMS has an optimizer with costs. There is no guarantee with which both conditions will be evaluated in the first place.

To apply this to your question: Yes, it may be benificial indexing your second column.

• Is it used as usual in the search?
• What will the implementation plan tell you?
• Will the access pattern change in the near future?
• How many records are in the table?
• Would there be a better option for the Covering Index?
• ...

Answer 3

When processing such a request, RDBMS will use only one index. The presence of separate indices in both columns will allow him to choose the one that will be faster.

Whether this is necessary depends on your specific situation.

• The question is slow, how is it now?
• Is it faster with an index in another column?
• Would it be faster with a single index containing both columns?

You may need to try and measure several approaches.

Answer 4

You do not need to specify a second column, but it can speed up your SELECT