CODEWITHSUNDEEP
mysqlindexing
Sai Sai
Sai Sai

Reputation: 41

Will indexing data speed up searching through mysql?

In mysql, if a column has two status and one result is little, should we build index for this column?

for example, sex column have male and female, and the female is minority. If we often search female data, build a index for sex will speed up?

Upvotes: 1

Views: 91

Answers (1)

Thomas G
Thomas G

Reputation: 10226

There's no clear answer on this one, it depends on multiple factors.

The general theory is that columns with low cardinality should not be indexed. In practice it's not black and white. Adding an index on a Boolean column sometimes drastically reduce the SELECT times.

The questions are:

  1. Do you have a lot of records in your table? If yes, adding an index will reduce your INSERT/UPDATE/DELETE times.

  2. What is the repartition between males and females? if 55/45, indexing won't help much. If 80/20 searching for the 20% of female using the index will be much faster.

  3. How will you query your males/females? If most of the time using other factors, then consider adding a composite index on your Gender + other(s) column(s)

The only approach in your case is testing. Create an index and see if it helps

Upvotes: 1

Related Questions