How much data can MySQL store?

mysql data limit

[sajjat]

I have a customer database of 85000 customer in MySQL. In my DB in store image of customer in blob field. my database size in 885 MB now.

I have to store 250000 customer info in that DB. IS their any limitation for MySQL datastorage? can it handle that volume of data?

[defcube]

I've done this before with gigs of data and had no problem, but that's on a huge beefy machine with 4g ram.

I have noticed it's better to put the blobs in their own table so the rest of the customer data doesn't pay for the table size increase if you need to look that up.

Also, highly consider writing a caching system so you don't keep loading the image data out of your database.

[sajjat]

Thanks ... we stored image in totally a different table. any more advice?

[finoo]

i sold a site earlier this year that had over 500 000 tables in one database. Over 8GB of data.

I don't think you have to worry.

Yes, backups aren't easy ;)

[urlreader]

I would say: save images into local directory, only save image path in mysql. This way, your database size is much smaller.

Average

[defcube]

urlreader - a lot of people I know have told me they prefer the method you mentioned. I still prefer keeping them in the database, but I add a caching layer so that I don't hit my database too hard.

Here are my reasons -- I'm curious to hear what you think of them. I'd love to get a friendly debate going about this:

#1 Extra hardware complexity: Many things I build run with multiple web servers. To store images on the hard disk, I'd need a shared storage device. It's simpler and requires less code to store them in the database.

#2 Referential integrity: My database can use foreign keys to verify the image blob actually exists. This prevents the images from becoming out of sync with the database.

#3 Ease of backups: I just back up my database. Source code is protected with subversion/cvs/perforce. No need to back up an images directory and keep that in sync with the DB.

#4 Reversability: If you change your mind later, you could always export your database to disk and drop that table.