CODEWITHSUNDEEP
mysqlindexing
Anuj Agrawal
Anuj Agrawal

Reputation: 1

Composite indexing

I'm having a table in database with column a,b,c,d,e. I created a composite index on column (a,b,c).I know that indexing will work in (a),(a,b),(a,b,c). But does indexing work in reverse order (c),(c,b),(c,b,a)??

Upvotes: 0

Views: 38

Answers (1)

Thomas G
Thomas G

Reputation: 10206

It depends.

If you use 

WHERE b = ? AND a = ? AND c = ?

MySQL should properly use your index. The order of the ANDs have no importance and the optimizer should rearrange that to find the right path.

However if you start with things like

WHERE b < ? AND a = ? AND c > ?

It will probably never use the index at all, or only partially.The optimizer will have to take a decision on wether doing a full table scan will be more efficient than partially use the index. That's rather not predictable here, as it depends on different parameters of your own DB : table structure, data, stats accuracy,...

So you might want to create several indexes for that purpose.

Note that if you are on MySQL 8+, you can also create descending index following the < or > you are expecting in your queries.

For my example above, this would probably work well :

CREATE TABLE t(
    a INT NOT NULL,
    b INT NOT NULL,
    c INT NOT NULL,
    INDEX bca_desc (b DESC, c ASC, a ASC)
);

Upvotes: 2

Related Questions