MySQL Index – Why Is MySQL Not Using the Index Here?

indexMySQL

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:

image

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?

Best Answer

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.

Related Question