How to to select data from a large MySQL table? Is there any best solution? 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?
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 &gt; $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.