Let’s look into selectivity, as this is an important topics when
looking at index performance. (Oooh, I said “performance”, watch
everyone’s ears perk up!).
This will probably answer the questions “Why isn’t MySQL
using my index?” or “Why is my query so slow when I have an index
Selectivity describes how different values of a field are. It is
a number from 0-1, although you can also think of it as a
percentage. A value of 1, or 100%, means that each value in the
field is unique. This happens with UNIQUE and PRIMARY keys,
although non-unique fields may have a selectivity of 1 — for
example, a timestamp value in a not-often-used table.
To calculate this, you take the total number of DISTINCT records
and divide by the total number of records.
My company has a large Users table, so I grabbed some statistics
off of that: