Search code examples
mysqlmultiple-columnsdatabase-indexes

Mysql: multiple column indexes, does the order of the multiple column index matter?

I'm trying to understand multiple-column indexes.

The question is: Does the order of the multiple column index matter?

I have a table with columns A, B, C and D.

I execute a query:

SELECT A FROM table WHERE D='2' AND B='1' AND C='0' ;

Is there a difference in speed if I create only one multiple column index:

  • B, C, D
  • D, B, C

Or doesn't the order of the multiple column index matter?

Extra question:

If I execute the following query, does it still benefit from the multiple column index?

SELECT A, B FROM tablename WHERE D='2' AND C='0';

Why (not)?

Solution

  • Order of the multi-column indexes doesn't matter. In other words following two queries are identical:

    SELECT A FROM table WHERE D='2' AND B='1' AND C='0';
SELECT A FROM table WHERE B='1' AND D='2' AND C='0';

    Your last query uses index [D, B, C] for D but not C as B isn't being used in WHERE:

    SELECT A, B FROM tablename WHERE D='2' AND C='0';

    This is due to the fact that first part (column B) of the index isn't used. You may find more details in the MySQL manual:

    MySQL can use multiple-column indexes for queries that test all the columns in the index, or queries that test just the first column, the first two columns, the first three columns, and so on.