3 ms·
Yeah 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.
by nhoughto 5y ago
Yeah 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'.