CODEWITHSUNDEEP
mysqloptimizationindexing
Danish Bin Sofwan
Danish Bin Sofwan

Reputation: 476

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)

Upvotes: 1

Views: 44

Answers (2)

1000111
1000111

Reputation: 13519

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

EDIT:

Sorry for missing other cases. Thanks to @Rick James.

Actually the all possible search combination among the columns A,B,C,D are as follows:

Length = 4

1.  A,B,C,D

Length = 3

2.  A,B,C
3.  A,C,D
4.  A,B,D
5.  B,C,D

Length = 2

6.  A,B
7.  A,C
8.  A,D
9.  B,C
10. B,D
11. C,D

Length = 1

12. A
13. B
14. C
15. D

Index Required (8):

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

Note: There are certain cases where multiple indexes can enroll to contribute. But the decision is left to the optimizer and the optimizer alone decides which index to use.

Upvotes: 1

Rick James
Rick James

Reputation: 142298

Perhaps 6 is the minimum to handle all 13 cases, each optimally:

The PK takes care of 1,2,3,6.
INDEX(A,C) takes care of 4.
INDEX(D,A) takes care of 5,13.
INDEX(B,C,D) takes care of 7,8,10.
INDEX(B,D) takes care of 9. (and 10)
INDEX(C,D) takes care of 11,12

In general, when threatened with a huge number of "needed" indexes, stop with 2 columns. For example, INDEX(B,C) will be nearly as good as INDEX(B,C,D). With that in mind, I would pick these 6 for the 15 combos:

AB;BC;CD;DA;AC;BD (Note that AB is already covered by the PK, so don't add it.)

Caveat: Your question states = on all tests. If, instead, you use a "range" (BETWEEN, <, etc) or IN or OR for any of the WHERE parts, this answer becomes less optimal.

Upvotes: 0

Related Questions