Danish Bin Sofwan Danish Bin Sofwan - 2 months ago 7
MySQL Question

MySQL : Applying index to benifit query with 4 user controlled conditions in a where clause

Suppose I have a simple query structure which looks like this :

Select columns,
FROM table
WHERE CONDITIONS


and I have 4 parameters: A, B, C, & D . These parameters define the search criteria. For example:

Select columns,
FROM table
WHERE A = 'something' AND B = 'something'


OR

Select columns,
FROM table
WHERE A = 'something' AND B = 'something' AND C = 'Something'


etc.

There can be several examples where every time I can define a different search criteria like the difference demonstrated between the two queries mentioned above.

Here are the different possible search combinations that I can apply to my
WHERE
CLAUSE

1. WHERE A = 'something' AND B = 'something' AND C = 'something' AND D = 'something'
2. WHERE A = 'something' AND B = 'something' AND C = 'something'
3. WHERE A = 'something' AND B = 'something'
4. WHERE A = 'something' AND C = 'something'
5. WHERE A = 'something' AND D = 'something'
6. WHERE A = 'something'
7. WHERE B = 'something' AND C = 'something' AND D = 'something'
8. WHERE B = 'something' AND C = 'something'
9. WHERE B = 'something' AND D = 'something'
10. WHERE B = 'something'
11. WHERE C = 'something' AND D = 'something'
12. WHERE C = 'something'
13. WHERE D = 'something'


What Indices can I define here ? keeping in mind I define the least number of indices and optimize all the search scenarios.

The primary key defined for the table is :

PRIMARY KEY(A,B,C,D)





EDIT

DATA TYPES are as follows :

A (VARCHAR)
B (VARCHAR)
C (INT)
D (DATE)

Answer

Since you are maintaining separate queries for each of the possible search combinations then to the best of my knowledge you need 4 indices.

You already have this one A,B,C,D (PK)

A,B,C,D index will be a use of the following search scenarios:

A,B,C,D
A,B,C
A,B
A

B,C,D index will be a use of the following search scenarios:

B,C,D
B,C
B

C,D index will be a use of the following search scenarios:

C,D
C

D index will be a use of the following search scenarios:

D
Comments