MySQL Forums :: Performance :: 30M rows - is dynamic sorting and selecting even possible?


Advanced Search

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
OR
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


Subject Views Written By Posted
30M rows - is dynamic sorting and selecting even possible? 3159 Craig Logan 04/14/2009 04:52PM
Re: 30M rows - is dynamic sorting and selecting even possible? 1892 Rick James 04/14/2009 07:10PM
Re: 30M rows - is dynamic sorting and selecting even possible? 1867 Craig Logan 04/15/2009 03:31AM
Re: 30M rows - is dynamic sorting and selecting even possible? 1879 Rick James 04/15/2009 10:33AM
Re: 30M rows - is dynamic sorting and selecting even possible? 1811 Craig Logan 04/15/2009 01:37PM
Re: 30M rows - is dynamic sorting and selecting even possible? 1333 Rick James 04/15/2009 10:00PM
Re: 30M rows - is dynamic sorting and selecting even possible? 1876 Craig Logan 04/16/2009 08:37AM
Re: 30M rows - is dynamic sorting and selecting even possible? 2011 Rick James 04/16/2009 12:17PM
Re: 30M rows - is dynamic sorting and selecting even possible? 2805 Craig Logan 04/16/2009 05:28PM
Re: 30M rows - is dynamic sorting and selecting even possible? 1846 Rick James 04/16/2009 09:43PM
Re: 30M rows - is dynamic sorting and selecting even possible? 1896 Craig Logan 04/17/2009 07:31AM


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.