Composite index

[Julian Assange]

Hello,

I am investigating why mysql optimizer does not utilise indexes that cover the entire data required by a query.

For example in a simple query on a myisam test table such as:

Select id,number from test where id = 3 and number = 9

where both columns are defined as int, id is the primary key and there is index1 (id,number) I would assume mysql optimizer would choose index1 so that it would not have to even touch the table and serve the result from the composite index. However looking at explain I can see that it uses the primary index to find the record hence potentially reading from the disk as opposed than from the index -ideally in RAM-.

The interesting thing is that I have used mysqlslap to test performance and, indeed, going with the primary index yields results faster. I have made the test with SQL_NO_CACHE and the result is the same.