Mysql

Database Sizes

[StanTheMan]

Does anybody know what the maximum number of records MYSQL can handle before a) falling over and b) becoming impractical.

I ask because I need to store roughly 2 million records (contacting just 2 fields) and I am wondering if MYSQL we be able to cope.

If anyone can shed some light, it would help a great deal.

Regards

[Paul in South Africa]

The answer depends to a certain extent on your operating system. This [mysql.com] might help you find the answer.

[Storyteller]

2 million isn't such a big number when it comes to number of records ;) Shouldn't be a problem for MySQL.

[lorax]

Handles large databases. We use MySQL Server with databases that contain 50 million records. We also know of users that use MySQL Server with 60,000 tables and about 5,000,000,000 rows.

From [mysql.com...]

[jmccormac]

I use it for testing out theories with the .com/net/org zonefiles. Though I tend to break the tables up alphnumerically, many of them have a few million entries. MySQL is able to handle it. However the limiting factor on tables this big would be more hardware related.

Regards...jmcc

[BCMG_Scott]

The number of records is not really the stopping point, but rather the average size of each record * the total records. If you have 50 million records that consist of a single 1 char field, then no problem. If you have 50 million records that have an average size of 1M then it could become more of an issue. The OS is where you want to start. Old versions of linux would limit you to a max file size of 2GB.

If you are going to have > 2GB in your datafile, then you might want to look into innodb (part of MySQL).

Scott

[lorax]

>> average size of 1M

LOL - If you have this condition then you need to rethink your situation and see if there's a better solution!

Your point is well taken.

[BCMG_Scott]

LOL - yeah average row size of 1M is a bit silly ;) If you got rows that size then you got a lot of other problems too.

Scott

[bcc1234]

Why is 1M silly? I don't know how mysql manages large objects, but you can easily have blob data over 1M a piece.

[lorax]

>> 1M blob

You're absolutely right bcc1234. I didn't even think of that. Probably because I never use blob data.

[jatar_k]

Unfortunately blob is often a necessary evil. I try to stay away from them but there are situations where there is no choice.

Though hopefully 1M/row wouldn't be a frequent situation. ;)

[BCMG_Scott]

It depends on what the blob is. I have seen people store the bin code of movies/images in a database. Personally I think that's a bad idea. Better method is to store the file on disk and reference the path to the file in the database. But then that's just a personal preference.

Even so, if you have an average size record of 1M you probably want to look at why? And you probably don't want to use standard MySQL. InnoDB would be a better thought or (if you have several thousand dollars just lying around) Oracle.

Scott