Skip navigation links

MySQL Forums :: Performance :: LIMIT, OFFSET too slow on very, very large table


Advanced Search

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.

Cheers,
Vincent

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

Options: ReplyQuote


Subject Views Written By Posted
LIMIT, OFFSET too slow on very, very large table 20494 Vincent Theeten 08/25/2006 01:13AM
Re: LIMIT, OFFSET too slow on very, very large table 7439 Toa Sty 08/25/2006 03:41AM


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.