I have millions (30-100 millions) of records with multiple columns. I need to sort this data on multiple columns and display them in pages. What is the best way to store this data?
Let us say if there is a table with columns T(id, c1,c2 ... cn). In MYSQL this query looks something like this:
select id from T order by cx, cy limit 1000, 1000
I can assume it is a read only table, but I need fast response.
Is there any advantage for this problem with NoSQL databases?
If your tables are properly indexed it would be fairly quick, even with millions or records. When you start adding conditions, like WHERE some_date < now() table partitioning might also help, but you have to plan ahead to fit partitions to your needs, otherwise it might slow it down instead.
NoSQL advantages are speed and flexible schema, but with millions of records it will require a significant amount of memory, plus in some cases like Redis it will require to have 50% of memory available for forking when it writes to disk etc. Is the response you are getting now not fast enough? Are your queries hitting correct indexes?
