Indexes, Low Index Selectivity and Index Performance

The other day I was talking to Jay about low index selectivity. One of the column in my users table had extremely low index selectivity (less than 0.0000005). However when running certain queries involving COUNT and utilizing that column, it seemed the index was being used as when I ran the same queries with IGNORE INDEX, the query execution time was significantly higher.

There have been posts earlier on PlanetMySQL that noted that in cases of extremely low index selectivity, MySQL's cost based optimizer will never use it.

Jay pointed out that using COUNT(column_name) in a query is a special case where even in case of extremely low selectivity, having an index speeds up the query. For all other queries not involving COUNT, the index will not be used if the selectivity was very low.

I will be posting the queries and further analysis in a later post.

You may want to see:

• Sheeri has a great post on Selectivity and Index Performance
• Measuring Index Selectivity


• Jay Pipes writes about calculating index selectivity using Information Schema
• Measuring index selectivity on Oracle
  
• Vadim writes about Indexes in MySQL
• Why does using an index takes my query longer