Let say we have a table with a large number of records. Now to show up all the data using pagination in a basic framework using MySQL query, we can use limits to get a piece of record.

... and so on ...

Though to my understanding LIMIT will work once the temporary result table is formulated or in other words, the search will go through all the rows once and then the final result will be populated. Firstly Am I right?

The query result takes much time to get data with limit and sometimes the system just reaches execution time limits. Is there any other better solution for this?

  • 1
    LIMIT without ORDER BY makes no sense. Anycase server must count records one-by-one until OFFSET then count and return LIMIT records (so LIMIT 0,100 is much faster then LIMIT 100000,100) then stop counting, but it is easier (and more fast) when the index applicable for specified ORDER BY expression exists. – Akina Jan 10 '20 at 6:39
  • Is there any best solution? There is not "best solution" for to select and paginate the whole huge table, especially without ordering. The only optimization is pre-pagination (pre-calculate and use PageNumber-IDsRange). – Akina Jan 10 '20 at 6:42
  • @Akina I've edited my question by adding ORDER BY and also checked for the result with your stated clause but still, my site takes too much time to retrieve results and sometimes reach execution limit. – Anant Jan 10 '20 at 6:45
  • @James Isn't it that WHERE clause will make to retrieve all the result once and then LIMIT will be applied to the resultant temporary data? – Anant Jan 10 '20 at 6:46
  • 2
    You might want to look here. – Vérace Jan 10 '20 at 6:52

The best solution for pagination is not to use OFFSET. Instead "remember where you left off". See http://mysql.rjweb.org/doc.php/pagination

However, there are cases where that is difficult. In your example of

    WHERE CONDITION                -- This

This works nicely. The 1st page will take only as long as fetching the 100 rows for it. And the 1000th page will be delivered as fast as the 1st.

    WHERE id > $left_off            -- where you left off
    ORDER BY id LIMIT 100;

But if the 'condition' is too messy, it may have to gather all the rows, and only then figure out where you left off. JOINs often add to the messiness.

If you would like to give the specific query, plus SHOW CREATE TABLE, we can discuss your situation.

LIMIT will work once the temporary result table is formulated

It depends on whether a single (possibly 'composite') index can short circuit things.

What you really need to worry about is "OFFSET must step over the skipped rows." That's why "left off" may work much better.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.