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)?
source to share
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.
source to share