I have a compound index on 3 columns. Now I want to execute the query based on only 2 columns, which both are not indexed. So the query takes long time to execute.
How to I make use of the compound index to alter my query?
Example : Table Temp
| Column A | Column B | Column C | Column D |
|---|---|---|---|
| Cell 1 | Cell 2 | Cell 2 | Cell 2 |
| Cell 3 | Cell 4 | Cell 2 | Cell 2 |
Now lets say I have compound index on column B, column C, and column D.
Now I want to select the rows based on only column b and column c
SELECT column A from Temp WHERE column b = 'XYZ' AND column c = 'ABC';
This doesnt use the index.
So is there a way I can make the query optimized so that the compund index is used.
I tried something like below, which does use the index but I want to remove this restriction since the value can be anything
SELECT column A from Temp WHERE column b = 'XYZ' AND column c = 'ABC' AND column d LIKE 'P%';
As stated in mysql documentation:
If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to look up rows. For example, if you have a three-column index on (col1, col2, col3), you have indexed search capabilities on (col1), (col1, col2), and (col1, col2, col3).
That means if your index is defined as (B, C, D) then query with condition like B = '...' AND C = '...' should use the index because (B, C) is leftmost prefix of (B, C, D). Query with conditions like C = '...' AND D = '...') or B = '...' OR C = '...' won't use that index.
Here is the example on db-fiddle