I've a database collection in MongoDB having around 6 million rows. The collection has three common keys. One of which acts as an index (has user ids). The second one is a string. But the third one, is a multidimensional array.
For every record, the multidimensional array is stored as array in MongoDB. I've index setup in MongoDB properly. The problem I have been facing recently is crashing of MongoDB for some reason. The backup script might be the reason, which runs every day. But I need to have daily backups.
I've some other collections in the database too. What I've been thinking is to use MySQL for this single table. Would this save space ( since MongoDB uses key:value relationship )? If yes, which engine would be preferred for a table which has 6 million records, one index, one VARCHAR and a column with data serialize() ed with php?
On the table in MongoDB, around 10 queries per second (on average) are fired. The only query that would run on this table in MySQL,except the data dump queries for backups, is
SELECT data FROM table where user = 'id'.
Also, I have memory limits. My server has 512 MB. It hosts an apache installation with 500-2000 pageviews daily. I've no problem boosting up the RAM, if it could promise stability.