I have a MySQL table having 22 fields or Attributes namely
Geo, Theater, Area, TotalSales
index on (geo, theater, area, TotalSales... and so on)
INDEX(geo, theater, area, ...) would only help if the first column (
geo, in this example) were included in the
WHERE clause. So that is not a good candidate.
22 separate indexes on each column is an ugly possibility, but may be close to 'best'.
Indexing a "flag" column (yes/no, M/F, ON/OFF, etc) is almost always useless. In general, if the index isn't going to limit the resultset to less than about 20% of the table, the Optimizer will decide that a table scan is faster than bouncing back and forth between the index and the data. So, this reduces the 22 by eliminating any low-cardinality columns.
On the other hand, 'composite' indexes are often good, even if they include a flag. You mentioned
INDEX(geo, theater) -- This would be very good if both are specified, and would also be good if only
geo were specified in the
WHERE. (It is useless if only
theater is specified`.) So, instead of 22 single-column indexes, think about 22 two-column indexes, where each of the 22 columns occurs once as the first column in the index.
But... Well, I don't want to copy my entire blog here, so let me send you to my index cookbook to discover how to make 'good' indexes.
But... That begs the issue of how many to make. 22 is "too many". At some point, the overhead of more indexes will outweigh the benefit. I suggest you look at what people are usually asking. Then make optimal indexes for the dozen most popular queries.