0

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.

SELECT * FROM TABLE_NAME WHERE CONDITION ORDER BY COLUMN1 LIMIT 0,100;
... 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 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 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 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 at 6:46
  • 2
    You might want to look here. – Vérace Jan 10 at 6:52
0

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

SELECT * FROM TABLE_NAME
    WHERE CONDITION                -- This
    ORDER BY COLUMN1 LIMIT 0,100;

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.

SELECT * FROM TABLE_NAME
    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.

|improve this answer|||||

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.