MYSQL INDEX FOR Multiple where columns combinations?

Question

Please help to make Mysql index For multiple where conditions Example :

Select id from table_name where col1='' and col2='' and col3='' and col4=''
and col5=''and col6='';

Now number of combinations of where conditions ,First two columns col1 and col2 is fix ; other combinations IS:-

where col1='' and col2='' and col3='' ;

where col1='' and col2='' and col3='' and col4='';

where col1='' and col2='' and col3='' and col4=''and col5='';

where col1='' and col2='' and col5=''and col6='';

where col1='' and col2='' and col4=''and col5=''and col6='';

where col1='' and col2='' and col4='' and col6=''

where col1='' and col2='' and col4='';

where col1='' and col2='' and col5='';

where col1='' and col2='' and col6='';

where col1='' and col2='' and col3='' and col5='';

I have only one Index

create INDEX `allpair` ON TABLE_NAME (col1,col2,col3,col4,col5,col6);

Please Help to make what is proper index for according these all where combinations. Thanks in advance.

Your index allpair will not cover every one of the queries in your list. If you have a composite index on (col1, col2, col3), then only where clauses on col1, (col1, col2), or (col1, col2, col3) will use the index. — Tim Biegeleisen
– Tim Biegeleisen, Commented Feb 28, 2018 at 10:50
@Tim Biegeleisen sir please show indexs and let me explain about it — kuldeep upadhyay
– kuldeep upadhyay, Commented Feb 28, 2018 at 10:52
I have no indices to "show" you unfortunately, I only commented to say that your composite index won't cover all the queries you mentioned. — Tim Biegeleisen
– Tim Biegeleisen, Commented Feb 28, 2018 at 10:52
@Tim Biegeleisen thn sir which index work for these conditions please — kuldeep upadhyay
– kuldeep upadhyay, Commented Feb 28, 2018 at 10:55

Bustikiller · Accepted Answer · 2018-02-28 10:56:57Z

4

The following column combinations would cover all your queries (I have removed the col prefix for simplicity):

 1  2  3 

 1  2  3  4

 1  2  3  4  5

 1  2  5  6

 1  2  4  5  6

 1  2  4  6

 1  2  4

 1  2  5

 1  2  6

 1  2  3  5

However, there are some indexes that cover more than one of the queries. For example, the index 1 2 3 4 5 already covers 1 2 3 and 1 2 3 4 because those columns are included in the leftmost part of the 1 2 3 4 5 index.

Thus, the column combinations you need would remove from the original list those elements which are leftmost contained in bigger elements:

 1  2  3  4  5

 1  2  5  6

 1  2  4  5  6

 1  2  4  6

 1  2  6

 1  2  3  5

answered Feb 28, 2018 at 10:56

Bustikiller

2,5402 gold badges20 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

kuldeep upadhyay Over a year ago

how to cover other 1 2 5 6 , 1 2 4 5 6 , 1 2 4 6 , 1 2 6 , 1 2 3 5

Bustikiller Over a year ago

You would need to add a new index by combination, unless the combination is already present in any of the existing indexes, starting from the left. Take into account that, for example, if you already have index 1 2 6 and you need to add index 1 2 6 5, the old one becomes dispensable.

kuldeep upadhyay Over a year ago

sir im litle bit confused how to make other index ,i try some index (12456), but for other please help

Bustikiller Over a year ago

Do you mean yo do not know how to make several indexes in a single table?

Bustikiller Over a year ago

You can have a look at stackoverflow.com/questions/30627258/…

|

Collectives™ on Stack Overflow

MYSQL INDEX FOR Multiple where columns combinations?

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related