2 ms·
Assuming 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 1
by branko_d 5y ago
Assuming 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'.