Why mysql not using the index here?

Question

I created a test table to try and test of course if index is working with a help of EXPLAIN EXTENDED command, here is the table:

id | first_id | second_id | details

and have 7 rows and data given are:

id | first_id | second_id | details
1  | 1        |           | hello
2  | 2        |           | hello2
3  | 1        |           | hello3
4  |          | 1         | hello4
5  |          | 2         | hello5
6  | 1        |           | hello6
7  |          | 1         | hello7

And my index is ALTER TABLE test ADD INDEX test (``first_id``) (Don't know how to add the back tick properly)

Then I tried to run this query:

EXPLAIN EXTENDED SELECT * FROM `test` WHERE first_id = 1 

And got this result:

From what I understand here, How can I tell if an index is being used to sort in MySQL?, mysql does not using the index.

Here is what I think the reason why mysql does not used the index.

1. The rows are too short
2. I made a mistake

If #1 is true, then how many rows do I need until mysql use the index? And if #2 is true, where I go wrong?

Answer 1

The percentage varies based on a lot of factors; I say "about 20%".

For tiny tables, you would be hard pressed to see any performance difference.

Usually the Optimizer "does the right thing" in deciding between using the index (and bouncing between the index's BTree and the data BTree) versus simply scanning the entire table (and throwing out unwanted rows).

Bottom line: Don't worry about it.

Answer 2

From the answer here, https://stackoverflow.com/questions/1969022/what-is-the-minimum-number-of-rows-required-to-create-an-index, it is stated that if you are selecting a rows that is under 5% of the total rows of that table (big tables), index will be used (assumed that there is one). And I proved it using my test table:

I add an index in details column:

ALTER TABLE test ADD INDEX details (`details`)

And tested it using this query

EXPLAIN EXTENDED SELECT * FROM `test` WHERE details = 'hello'

And I got this:

And to clear my doubt, I changed the hello7 to hello from details column and run the query again I got this:

This means that index will be used if the total # of rows of your column's data ex. total number of this -> WHERE column = data is below 50% (based on my test table).

Simplified

if query_results_count < 50% of the total results of the table = index
will be used, otherwise it will use full-table scan.

Note: I based my answer on what I tested in my localhost and with the help of the links I mentioned. Make sure to make a test before applying it to your production site/s.