4 ms·
I was curious how this method avoided the wall you will hit on scanning. Answer appears to be that it doesn't?
by taeric 5y ago
I was curious how this method avoided the wall you will hit on scanning. Answer appears to be that it doesn't?
- nhoughto 5y agoYeah it doesn’t, the mistake is thinking the “select *” is the expensive bit, for limit 15 it’s cheap, it’s the offset 150000 that is expensive deferred or not.
- branko_d 5y agoAssuming a non-clustered index on FOO (and no predicate pushdown by an optimizer smarter than MySQL's), the first query will scan the index on FOO and perform 150000 seeks into clustered index (only to discard 149985 of them): SELECT * FROM T ORDER BY FOO LIMIT 15 OFFSET 150000 But the following will just scan the index: SELECT FOO FROM T ORDER BY FOO LIMIT 15 OFFSET 150000
- nhoughto 5y agoyep its more complicated than my simple statement makes it out =) i guess the core of the statement was that the net cost to the db is more tied to offset than select, yes they interact and 'select * with offset' is more expensive than 'select foo with offset', but its really the offset that is the problem. If you did keyset style pagination and still did 'select *' you'd be fine and roughly the same cost as 'select foo'.
- branko_d 5y agoIt still scans the index, but it doesn't fetch those rows that will be discarded. After it reaches the right page, it fetches the rows themselves. So it doesn't exactly avoid the wall, but it can push it considerably further away.