Why mysql not using the index here? 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.


The rows are too short
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?
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.