CODEWITHSUNDEEP
mysqlindexingexplainmultiple-columns
mojuba
mojuba

Reputation: 12227

Efficiency of multicolumn indexes in MySQL

If I have a MyISAM table with a 3-column index, something like

create table t (
  a int,
  b int,
  c int,
  index abc (a, b, c)
) engine=MyISAM;

the question is, can the following query fully utilize the index:

select * from t where a=1 and c=2;

in other words, considering that an index is a b-tree, can MySQL skip the column in the middle and still do a quick search on first and last columns?

EXPLAIN does seem to be showing that the index will be used, however, the Extra says: Using where; Using index and I have no idea what this really means.

Upvotes: 1

Views: 192

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

The answer is "no".

The MySQL documentation is quite clear on how indexes are used:

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). (http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html.)

What happens is that the index gets used for "a=1". All records that match are loaded, to see if "c=2" is true. The filter ends up using a combination of indexes and explicit record filtering.

By the way, if you want to handle all combinations of two columns, you need several indexes:

  • (a, b, c)
  • (b, a, c)
  • (c, b, a)

Upvotes: 3

sundar
sundar

Reputation: 1760

Even if you are using an index for all parts of a WHERE clause, you may see Using where if the column can be NULL.

As per MySQL documentation, the above statement clarifies that the column in your table could be null and hence it says using where as well though it has covering index for fields in where clause.

http://dev.mysql.com/doc/refman/5.1/en/explain-output.html#explain-extra-information

Upvotes: 0

Related Questions