CODEWITHSUNDEEP
mysqloptimizationindexing
Dewan159
Dewan159

Reputation: 3084

Should i use an index on a column with limited number of values?

Reading this I now understand when to use indexes and when not to use them. But i have a question; would using an index on a column with a limited number of possible values help speedup queries (SELECT-ing) ? Consider the following:

Table "companies": id, district_id, name
Table "districts": id, name

The number of districts would never pass 5 entries. Should i use an index on companies.district_id then or not? I read somewhere (can't find the link :( ) that it wont help since the values are not that many and it would actually slow down the query in many cases.

PS: both tables are MyISAM

Upvotes: 3

Views: 1898

Answers (1)

Rick James
Rick James

Reputation: 142560

Almost never is an INDEX on a low-cardinality column used by the optimizer.

On the other hand, a "compound index" may be useful. For example, does INDEX(district_id, name) have any use?

Having INDEX(district_id) will slow down INSERTs because the index is added to whenever a row is inserted. It will not slow down SELECTs, other than the minor amount of time for the Optimizer to notice the index and reject it.

(My statements apply to both MyISAM and InnoDB.)

More discussion of this answer: MySQL: Building the best INDEX for a given SELECT: Flags and Low Cardinality

Upvotes: 4

Related Questions