Archive for February 11th, 2011
Answering questions asked on the site.
Thanks for your article about late row lookups in MySQL.
I have two questions for you please:
- Is this workaround specific to MyISAM engine?
- How does PostgreSQL handle this?
The questions concerns a certain workaround for MySQL
LIMIT … OFFSET queries like this:
SELECT * FROM mytable ORDER BY id LIMIT 10 OFFSET 10000
which can be improved using a little rewrite:
SELECT m.* FROM ( SELECT id FROM mytable ORDER BY id LIMIT 10 OFFSET 10000 ) q JOIN mytable m ON m.id = q.id ORDER BY m.id
For the rationale behind this improvement, please read the original article.
Now, to the questions.
The second questions is easy: PostgreSQL won’t pull the fields from the table until it really needs them. If a query involving an
ORDER BY along with
OFFSET is optimized to use the index for the
ORDER BY part, the table lookups won’t happen for the records skipped.
Though PostgreSQL does not reflect the table lookups in the
EXPLAIN output, a simple test would show us that they are done only
LIMIT times, not
OFFSET + LIMIT (like MySQL does).
Now, let’s try to answer the first question: will this trick improve the queries against an InnoDB table?
To do this, we will create a sample table: