anijit anijit - 1 year ago 114
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

I want to get
may be for a
or a
or an
or maybe
for a particular
of a particular
and so on.

The table consists of almost

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


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

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download