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.