MySQL: Indexing large amount of columns (150M rows) with varied queries
Basically I'm working on a large table (150M rows) so indexes are pretty important, but it's basicall开发者_开发技巧y 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.
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.
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?
精彩评论