Search code examples
mysqlselectindexingexplain

MySql Explain ignoring the unique index in a particular query

I started looking into Index(es) in depth for the first time and started analyzing our db beginning from the users table for the first time. I searched SO to find a similar question but was not able to frame my search well, I guess.

I was going through a particular concept and this first observation left me wondering - The difference in these Explain(s) [Difference : First query is using 'a%' while the second query is using 'ab%']

[Total number of rows in users table = 9193]:

1) explain select * from users where email_address like 'a%';

enter image description here

(Actually matching columns = 1240)

2) explain select * from users where email_address like 'ab%';

enter image description here

(Actually matching columns = 109)

The index looks like this : enter image description here

My question: Why is the index totally ignored in the first query? Does mySql think that it is a better idea not to use the index in the case 1? If yes, why?

Solution

  • If the probability, based statistics mysql collects on distribution of the values, is above a certain ratio of the total rows (typically 1/11 of the total), mysql deems it more efficient to simply scan the whole table reading the disks pages in sequentially, rather than use the index jumping around the disk pages in random order.

    You could try your luck with this query, which may use the index:

    where email_address between 'a' and 'az'

    Although doing the full scan may actually be faster.