Multiple Column Index Combination?

[PeerFly]

I have an issue that I can’t figure out. I have a huge query that searches on columns: a,b,c,d,e,f pulling from a DB of nearly 5 million records. I can make a multi column index on a,b,c,d,e,f but the query is dynamic because it may search on a,b,e,f or b,d,f. The amount of indexes I would need to create for this would probably be 36 (because of the leftmost prefix rule). I know that is rather absurd. So, how can I make sure everything is indexed so searches are fast on this large query and I don’t have to make all the indexes?

 

I don't want to have to do this:

 

a,b,c,d,e,f

b,c,d,e,f

c,d,e,f

d,e,f

e,f

f

 

a,f

a,e,f

a,d,e,f

 

And so on and so forth..... This would not only take a long time to do on 5 million rows, it would take an insane hit when a row is inserted/deleted because of all the indexes.

[abazoskib]

The few options I can think of that might help:

 

- normalize your table into seperate tables(watch out for joins!)

- create indexes on the most queried columns, and create multiple index on columns that usually get queried together

- create indexes to create your own "partitions". if you have one column that has a possible 4 unique values, and another 6, but the rest of the columns hold completely random data, then index on those two columns to split the data up quickly during a select.

- shorten the length of the data being searched. in other words, optimize the data types and sizes of those columns. use only what you need.

 

if i think of more ill post, but its hard to plan it out without more detail. post a describe or create table of your table. also data ranges.

[Mchl]

It seems that if you have index on columns c1,c2,c3,c4 and run a query like:

SELECT * FROM table WHERE c1=c1 AND c2 = $value2 AND c3 = $value3 AND c4 = $value4;

then the index will be used (or so EXPLAIN claims)