Mysql: multi-column indexes, is the ordering of multi-column column values?
I am trying to understand multi-column indexes.
Question: Does index order mean multiple columns?
I have a table with columns A, B, C and D.
I execute the request:
SELECT A FROM table WHERE D='2' AND B='1' AND C='0' ;
Is there a speed difference if I only create one index with multiple columns:
- B, C, D
- D, B, C
Or is the order of the multi-column column value not important?
Additional question:
If I ran the following query, will it take advantage of the multi-column index?
SELECT A, B FROM tablename WHERE D='2' AND C='0';
Why not)?
The order of multi-column indexes does not matter. In other words, the following two requests 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';
The last query uses the index [D, B, C] for D
, but not C
because it is B
not used in WHERE
:
SELECT A, B FROM tablename WHERE D='2' AND C='0';
This is because the first part (column B) of the index is not used. You can find more details in the MySQL manual :
MySQL can use multi-column indexes for queries that check all columns in the index, or queries that check only the first column, the first two columns, the first three columns, etc.