MySQL Index sometimes not being used

Question

I have a table with 150k rows of data, and I have column with a UNIQUE INDEX, It has a type of VARCHAR(10) and stores 10 digit account numbers.

Now whenever I query, like a simple one:

SELECT * FROM table WHERE account_number LIKE '0103%'

It results 30,000+ ROWS, and when I run a EXPLAIN on my query It shows no INDEX is used.

But when I do:

SELECT * FROM table WHERE account_number LIKE '0104%'

It results 4,000+ ROWS, with the INDEX used.

Anyone can explain this?

I'm using MySQL 5.7 Percona XtraDB.

Answer 1

30k+/150k > 20% and I guess it is faster to do table scan. From 8.2.1.19 Avoiding Full Table Scans:

The output from EXPLAIN shows ALL in the type column when MySQL uses a full table scan to resolve a query. This usually happens under the following conditions:

You are using a key with low cardinality (many rows match the key value) through another column. In this case, MySQL assumes that by using the key it probably will do many key lookups and that a table scan would be faster.

If you don't need all values try to use:

SELECT account_number FROM table WHERE account_number LIKE '0103%'

instead of SELECT *. Then your index will become covering index and optimizer should always use it (as long as WHERE condition is SARGable).