NoSQL Recommendation for a specific need

Question

I'm using a MySql table with the following simple structure :

ID_A : int 8

ID_B : int 8

Primary key : ID_A, ID_B Index : ID_B
This MySQL table contain more than 500 million of rows, and the weight is 20Go.

I need to be able to perform those kind of query :

select *,count(*) as cpt from table group by ID_A order by cpt DESC

select *,count(*) as cpt from table group by ID_B order by cpt DESC

select * from table where ID_A in (1,2,3,4,5,5) 

select * from table where ID_B in (1,2,3,4,5,5) 

select *,count(*) as cpt from table where ID_B in (1,2,3,4,5) group by ID_A order by cpt DESC

select *,count(*) as cpt from table where ID_A in (1,2,3,4,5) group by ID_B order by cpt DESC

I tried innodb and MyIsam, but even with a big configuration server, mysql can't answer the Group By query. I even can't do it from a script side, because it would consumme to much memory.

All the data can't fit in RAM (20Go today, but 60Go in a near future).

Should we use NoSql database ? MongoDB ? Map reduce DB ?

Thanks for you help

chafank chafank · Accepted Answer · 2011-07-13T08:40:44

I've never used MongoDB for large data, but for more than 10,000 keys you can use the mongoDB map/reduce instead of default groupBy.

You can found here mongoDB documentation for this:

mongoDB groupBy for larger grouping operation

Hope this can help

NoSQL Recommendation for a specific need

2 Answers