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