Using OFFSET in a large table by a simple sub-query
Stack Exchange network consists of 183 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.
Ask questions, find answers and collaborate at work with Stack Overflow for Teams. Explore Teams
The slow performance for using OFFSET in large table has been widely discussed in various blogs, and the most efficient way is to use an INNER JOIN as
In the following example, the ORDER BY is assumed the primary key, but obviously other orders or even WHERE clause can be added. My question is about comparison in performance of INNER JOIN or a simple sub-query`.
In a LEFT JOIN query, you can dictate what keys you want and the order you want to fetch and join, while INNER JOIN lets the MySQL Query Optimizer decide which is best. In some cases, the EXPLAIN plan may look horrible but still give good performance.
This way, you should be able to navigate (or paginate) through the keys before locating actual data. To show this using empirical evidence, see my StackOverflow post (Fetching a Single Row from Join Table) on using LEFT JOIN with primary keys.
