MySQL InnoDB Weird Query Performance

I designed two tables, both using InnoDB.

The first one has columns “Offset, Size, and ColumnToBeIndexed” with BIGINT “Offset” being the primary key, and the second one has columns “OffsetAndSize, and ColumnToBeIndexed” with BIGINT “OffsetAndSize” being the primary key. And there are both 15 millions rows in each table.

I added indexes to both tables on “ColumnToBeIndexed.

My two queries for them are

SELECT Offset, Size 
FROM someTable 
WHERE ColumnToBeIndex BETWEEN 20 AND 10000 
ORDER BY Offset ASC

and

SELECT OffsetAndSize 
FROM someTable 
WHERE ColumnToBeIndex BETWEEN 20 AND 10000 
ORDER BY OffsetAndSize ASC

Because the second query could use the secondary index and does not need to look up the clustered index to find the “size” information, I expected that the second query on the second table performs better than the first query on the first table. However, as the test came out, it turned out that the first query performs better every single time.

Does anybody out there know what seems to be the problem?

Go to Source
Author: Bruce