What DB for big databases?

Question

I'm embarking on a project which will likely cross several million rows in the short future, so I am researching the database I use as that is sure to prove an issue. From what I have read, SQL in all its incarnations has issues once you get to the 2,000,000 rows issue for a table. Is there a good database recommended for these big size projects?

It is a website I am talking about, and archiving old entries is not ideal, though can be done if it proves to be an issue that I can't overcome.

Thanks.

Answer 1

No database that would call themselves an SQL database if they had issues with 2 million records. You can get in trouble with some databases with 2 billion records though.

I've had mysql databases with well over 150 million records without trouble. You need to figure out what features you need from a database before you're deciding, not ponder over a few million rows - which is not much at all.

Answer 2

First off, a million records is not exactly a lot when databases are concerned. Any database worth it's salt should be able to handle that just fine.

Create proper indexes on your tables and almost any database will be able to handle those numbers of records. I've seen MySQL databases with millions of rows that worked just fine, and MySQL is not a heavyweight in database land.

MS SQL Server, PostgreSQL, DB2, Progress OpenEdge - almost anything will do if you create proper indexes. Things like MS Access (and possibly sqlite) may fall apart when you put a lot of data in them.

Answer 3

I've had tables in MS SQL Server with a fair bit more than 2 million rows without trouble. Of course, it depends on how you're using that data.

Just don't try using MySQL for something like this. At least from my experience, it just doesn't allow enough tweaking to provide high enough performance. I've run into a few cases with large amounts of data in (almost) identically set up tables. MySQL5 performed like 30 times slower than SQL Server on the same hardware. Extreme example maybe, but still.

I have too little experience with PostgreSQL or Oracle to judge, so I will just stick with not recommending MySQL. Or Access ;)