2gb table with 10 million rows, late pagination select is slow

Question

I have table in MySQL with 10 million rows with 2 GB data selecting IN LIFO format data is slow

Table engine is = InnoDB

table has one primary key and one unique key

SELECT * FROM link LIMIT 999999 , 50;

how I improve the performance of the table. ?

table structure

id  int(11) NO  PRI NULL    auto_increment
url varchar(255)    NO  UNI NULL    
website varchar(100)    NO      NULL    
state   varchar(10) NO      NULL    
type    varchar(100)    NO      NULL    
prio    varchar(100)    YES     NULL    
change  varchar(100)    YES     NULL    
last    varchar(100)    YES     NULL

NOTE: SELECT * FROM link LIMIT 1 , 50; is taking .9ms but current sql is taking 1000ms its 1000 time taking more

Answer 1

This most likely is due to "early row lookup"

MySQL can be forced to do "late row lookup". Try below query

SELECT  l.*
FROM    (
        SELECT  id
        FROM    link
        ORDER BY
                id
        LIMIT 999999 , 50
        ) q
JOIN    link l
ON      l.id = q.id

Check this article

MySQL limit clause and rate low lookups