I've been messing around with query performance for a system with pagination to make the data selection as fast as possible, but I've come across something I don't quite understand. To my knowledge, when a limit with an offset is used, MySQL has to iterate through each row before the offset and then discard them, so in theory a query with an offset of 10,000 would be much slower than one without, which is normally true as in this case
select SQL_NO_CACHE * from `customers` where `NetworkID`='\func uuid()'
order by `DateTimeAdded` desc limit 0, 100;
/* finishes in 2.497 seconds */
select SQL_NO_CACHE * from `customers` where `NetworkID`='\func uuid()'
order by `DateTimeAdded` desc limit 10000, 100;
/* finishes in 2.702 seconds */
But, if I use an inner join to join the table to itself with only UserID column for doing the sorting and limiting, it's consistently faster with the offset of 10,000 than without one, which completely stumps me. Example here would be
select SQL_NO_CACHE * from `customers`
inner join (select `UserID` from `customers` where `NetworkID`='\func uuid()'
order by `DateTimeAdded` desc limit 100)
as `Results` using(`UserID`)
/* finishes in 1.133 seconds */
select SQL_NO_CACHE * from `customers`
inner join (select `UserID` from `customers` where `NetworkID`='\func uuid()'
order by `DateTimeAdded` desc limit 10000, 100)
as `Results` using(`UserID`)
/* finishes in 1.120 seconds */
Why is the query using the offset always faster than the query without the offset?
Explains:
I have posted a Google Docs spreadsheet here with the explains content here
Note: The tests above were done in PHP looping 20 times each
Note2: customers is a view, not a base table
optimizethe table and see if it is the same (neutrlize all unknown factors first)