Which database(s) should I use?

Question

Hi,

I'm building an application that needs to query a lot of data that is written once and not changed anymore. Should I use MySQL for that or should I use something like SimpleDB or BigTable? (I need to write once, read many times)

Thank you.

Edit: I want to use Heroku, big for me is more than 5MB. "Thousands of rows" take more than 5MB. That's why I'm wondering if I should use CouchDB, SimpleDB or MongoDB in order not to pay the $15 that Heroku charges. Suggestions to overcome this? Thank you all for the comments!

Answer 1

I think you should use non transactional and document oriented database like MongoDB or CouchDB .

Answer 2

More important than your choice of database engine is your table structure. You should read up on OLAP database structure. Another consideration is the language you are writing in, make sure there is good support for the API of the database you want to use. CouchDB would be good as it has very low overheads due to the lack of relations/transactions.

Answer 3

What does it mean "a lot of data"? Thousands, millions, billions of rows? How many and what columns per row? Will you use many joins or simple selects?

If your tables are simple or you need to use complicated JOINs, I would pick any SQL you're familiar with.

If your structure is complicated and if document oriented database would suit your needs, I would pick MongoDB (preferred) or CouchDB.

Edit: According to your comment - thousands of rows is not that much. Use your favourite database and set as much cache as it needs (read more about necessary cache amount or start a new topic). Or use Memcached but I suggest to use database cache as it's efficient too and painless for you. Goog luck man!

Answer 4

For "write once, read many times", de-normalized database (that does not waste cycles to do joins etc.) is a good choice.

So, you should design your tables such that these reads have to do minimum number of I/Os and joins. You can do that with any database. It is the structure of your tables that matters.

AFAIK, SimpleDB and BigTable are distributed databases and offer very good query speeds if your users are distributed geographically (thereby bypassing the network latencies). They will not offer much advantage if the I/O latency is not the bottleneck.

Answer 5

The amount of data you have is tiny. Any DBMS will cope with a few thousand rows. I suggest you look first at one of the popular SQL DBMSs - such as MySQL, which you already mentioned. You need to make the choice based on the functional requirements rather than be concerned with the data size.