anijit anijit - 9 days ago 8
MySQL Question

Collective Index or Individual index which is fastest for data accessing

I have a MySQL table having 22 fields or Attributes namely

Geo, Theater, Area, TotalSales
etc.

I want to get
TotalSales
may be for a
Geo
or a
Theater
or an
Area
or maybe
for a particular
theater
of a particular
Geo
and so on.

The table consists of almost
302245
records

So, how to index it for faster access?
Should I put an Index individually on each of the field namely
geo
,
theater


or,

Should I put an index on all 22 fields collectively like,
index on (geo, theater, area, TotalSales... and so on)

Answer

A composite 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.