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