indexing - Understanding multiple column indexes in MySQL query

Question

Here is the query:

SELECT * FROM table WHERE accountid = 1 ORDER BY logindate DESC LIMIT 1

Now if I added an index with multiple columns on the fields:

INDEX(accountid,logindate)

Would MySQL take advantage of this multiple column index? Or would it not use it because one field is in the where clause and the other is in an order statement? Or does it not matter as long as I use the fields in the order of the multiple column index?

Answer 1

Good question.

Indexes work left to right, so your WHERE criteria would use the index. The sort would also utilize the index in this case (execution plan below).

From the manual:

The index can also be used even if the ORDER BY does not match the index exactly, as long as all of the unused portions of the index and all the extra ORDER BY columns are constants in the WHERE clause. The following queries use the index to resolve the ORDER BY part:

SELECT * FROM t1
WHERE key_part1=constant
ORDER BY key_part2;

If you had a single column index (accountid), a filesort would be used instead. Therefore, your query does benefit from that index.

---

Two Column Index

create table t1 (
  accountid tinyint,
  logindate date);

create index idx on t1 (accountid, logindate);

insert into t1 values (1, '2012-09-05'), (2, '2012-09-09'), (3, '2012-09-04'), 
    (1, '2012-09-01'), (1, '2012-09-26'), (2, '2012-05-16'), 
    (1, '2012-09-01'), (3, '2012-10-19'), (1, '2012-03-01')

Execution Plan

ID  SELECT_TYPE  TABLE  TYPE  POSSIBLE_KEYS  KEY  KEY_LEN  REF   ROWS  FILTERED  EXTRA
1   SIMPLE       t1     ref   idx            idx  2        const 5     100       Using where; Using index

Single Column Index

create table t1 (
  accountid tinyint,
  logindate date);

create index idx on t1 (accountid);

insert into t1 values (1, '2012-09-05'), (2, '2012-09-09'), (3, '2012-09-04'), 
    (1, '2012-09-01'), (1, '2012-09-26'), (2, '2012-05-16'), (1, '2012-09-01'), 
    (3, '2012-10-19'), (1, '2012-03-01')

Execution Plan

ID  SELECT_TYPE  TABLE  TYPE   POSSIBLE_KEYS  KEY  KEY_LEN  REF   ROWS  FILTERED  EXTRA
1   SIMPLE       t1     range  idx            idx  2              5     100       Using where; Using filesort