MySQL Forums
Forum List  »  Performance

30M rows - is dynamic sorting and selecting even possible?
Posted by: Craig Logan
Date: April 14, 2009 04:52PM

Hey guys,

my questions is this: I have a table containing more than 30M rows, the structure is something like
id_1 int unsigned (PRIMARY)
id_2 mediumint unsigned (INDEX)
id_3 int unsigned (INDEX)
var1 char(4)
var2 tinyint unsigned (INDEX)
var3 datetime (INDEX)
+ 8 mediumint columns that I could move to a different table if I have to

now I need to to dynamic sorts over multiple colums and then select some rows from the middle of the result, something like

SELECT * FROM table ORDER BY var1 ASC, datetime DESC LIMIT 2500000,10000
SELECT * FROM table ORDER BY id_2 DESC, datetime DESC LIMIT 2500000,10000

now every time I use more than one column in my order by command, mysql resorts to a filesort. "limit 2500000,10000" by itself is of course painfully slow.

so: is it even possible to do realtime (<1 second) sorting and selecting of rows for such large tables?

thanks in advance for your help.

Options: ReplyQuote

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.