MySQL: Indexing large amount of columns with varied queries

Question

Hey guys.... I really hope someone can help me here as I can't seem to get an answer anywhere :(

Basically I'm working on a large table so indexes are pretty important, but it's basically a table that has several different fields that are searchable..... so the way the columns are queried has probably 100's of different variations, so creating indexes for each different variation isn't realistic.

So to explain a bit more..... sometimes a column may appear in the query, sometimes it won't.... this is true for several different columns.

Thanks for any help!

Answer 1

Only solution pretty much is one index per table, then let the optimizer sort it out (i.e. decide which index to use). What database, and what is "quite large table" for you? 100 million rows? 1 billion rows?

Answer 2

No matter how complex the table, for most queries there are probably a few key columns that are almost always used in your queries. Just index those ones. Row scans are inevitable it sounds like but if you have indexed the key columns, MySQL is smart enough to use the index that will narrow down the potential rows to be scanned automatically.