php video and photo uploading: storing in database vs. uploading

Go To StackoverFlow.com

2

I am going to be approaching a large site in the near future that will require hundreds, maybe thousands of videos to be uploaded and stored. In addition, many images will need to be uploaded but not nearly as much, and they will be small in size. Up until now, I've always used php and mysql as follows:

  • upload an image
  • store the file name in the database
  • reference a folder + filename in the database to display file

However, I've done some research on some BLOB storing images and files in the database and unsure of which way is best for this potentially large-scale project. Should I continue uploading the way I've been, or is it more efficient to use the BLOB type in mysql? I'm afraid that by storing lots of videos and images in a database, it could get to be too much data or too slow, but I could be completely wrong. Please let me know any and all suggestions you have.

2012-04-05 16:45
by Drew Bartlett
For every video you store in a BLOB a kitten will perish! - Lawrence Cherone 2012-04-05 16:49
You've got a very valid point there - Drew Bartlett 2012-04-06 14:31


3

Ok, so the two big areas are: backup, and change

Backup

Depending on the DB engine you choose, backups require locking / dumping / unlocking. The bigger the database the longer the lock will be there. You could use some features (master/slave) to allow hotsyncing (backups without the locks) but there's a risk the backup won't completely cover the dataset, and the scale of the DB is still a factor. If large files are simply files (rather than DB BLOBS), it's just a matter of making a backup strategy for the file system, and since they're probably static this just means having multiple copies stored (this could even be by design at upload - store on server A and server B).

Change

Do you expect your system to stay the same? It sounds like you will have scaling issues. Might you move the DB to another server, the file storage to another server or SAN (or S3 etc) as your system grows? If you've got it all locked in the DB you have to rely on DB only solutions to deal with the swelling size (large expensive servers, master/slaves etc). Of course you could store the files in one DB (less backup requirements, doesn't mess around with your data DB) and other data in another... but a filesystem is a DB of sorts.

So

So for backups and scaling storing large files as files and not BLOBs is a better solution. If the files were small (low MP JPGs for example) the balance may shift. But for large files there's no point in the overhead of DB processing, and the extra load requirements it puts on the DB server. Keep 'em separate.

2012-04-05 16:59
by Rudu
Awesome! I'm really glad you explained that to me! It helps a ton - Drew Bartlett 2012-04-05 18:32


5

Storing images in BOLB is not efficient. It will be difficult to manage. Your database will become large.

It will be slower then serving a file from the server with a PHP script

2012-04-05 16:49
by NoName
Agreed. I wouldn't put images in a bolb either ;-)halfer 2012-04-05 17:05
Great thanks very much for the advice! : - Drew Bartlett 2012-04-05 18:31
Ads