30M rows - is dynamic sorting and selecting even possible?

[Craig Logan]

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.