I've worked with databases larger then 50,000,000 rows in MySQL.
There is no hard limit on data in MySQL itself. There are many other considerations which must be made in "determining" a maximum.
How large of a file can your file system handle (many file systems can't handle files over 2GB or other sizes).
The other issue is whether the machine has enough ram to process queries on a database of that size. While MySQL is very fast and powerful for databases that are up to a few hundred megabytes in size (total), I find that it becomes incredibly slow with larger databases.
In one particular case, I was loading a database with 50,000 records at a time. The reason I was loading in such small chunks was because each single record in the core table would also trigger inserts of 50 - 200 rows into another table.
At 450,000 records loaded (sorry, I didn't pay particular attention to the size of the expanded table which has 50 - 200 rows per main record), queries on the expanded table were still taking less then 1 second to return results. I loaded the next 50,000 records and query response time dropped to 20 - 30 seconds.
I thought surely something had to be wrong, cleared the database, and started over. Nearly identicle results.
I ultimately ended up hiring the MySQL support team to look into it, they tuned the database, server, etc., and ultimately recommended that I get more ram. So, I doubled the system ram from 512MB to 1GB.
Started loading again. I got to about 750,000 records in the main table with under 1 second queries. Loaded another chunk and guess what, 20 - 30 second queries again.
I increased ram from 1 GB to 2 GB and went from 30 second queries to 1.5 second queries.
I noticed a direct correlation between the size of all of the database files combined and the available memory on the system. Essentially, as long as the available memory exceeds the size of any given table (index) or hopefully, the entire database itself, MySQL doesn't seem to have any limits, however, the moment the database or more specifically a frequently used index is larger then available memory, MySQL runs dogged slow. There's a small margin of difference from one system to the next, but this has been pretty consistant in all clients I've worked with since (and I do frequently work with very large databases (well over 2 Gš because I've worked primarily with search engines for the last couple of years.
Sorry for the long explanation, but I thought my days of trouble and expense of hiring the MySQL team to be told "add more ram" and later conclusions may save you guys some time and help to be more specific on the "how big" question with regards to a MySQL database.
I've had databases running at reasonable performance to around 3 - 3.5 GB of data (assuming 4GB ram, high performance drives, etc.).
Anything larger then that, you'll need to consider enterprise setups which are out of my league (at current anyway š).
Scott A. Hammond, Sr., CEO
Prima Internet, Inc.
