MySQL Forums
Forum List  »  Performance

LIMIT, OFFSET too slow on very, very large table
Posted by: Vincent Theeten
Date: August 25, 2006 01:13AM

Hi all,

We moved from MS SQL to MySQL about 6 months ago; and up to now not a single regret.
In that 6 months we have accumulated a database with close to 3 million records.
In our application we need to be able to select chunks of rows in the middle of the table.

SELECT **Only my PK field** FROM **table** LIMIT 1000 OFFSET 2500000

It worked well with <1 million records, but now takes up to 10 minutes to complete.

I've indexed the columns we use in the select, and I don't see a way to optimize this.

Can it still be done with LIMIT OFFSET with over 3 million records?
Do I need an extra autoincrement field that holds the rownumber and use WHERE?
Or do I need to start partitioning my data? If yes, how?

Any input is welcome.


PS: Our DB server is a dual 3Ghz (dual core) with 4Gb of RAM running only MySQL

Options: ReplyQuote

Written By
LIMIT, OFFSET too slow on very, very large table
August 25, 2006 01:13AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.