Storing images in a mysql database can be a great thing, not only is everything easily accessible, it is also password protected and has all the necessary details right on the same row, great for websites where visitors upload their own photos (like vacation photos, party photos)
it also allows me to easily backup all the photos in one database file.
But recently Ive heard from forums that storing images in databases really impacts the image fetching performance for the visitor.
is this true?
and what are the other pros and cons of storing images in a database?
read that, interesting, but why do some still support images in database?
For a small site, with a small database, and no or few concurrent users, it doesn’t matter. You can do whatever’s easier for you.
It doesn’t scale, though.
thanks for the tip Dan, ive worked always with pure images on filesystem but at a certain point i thought it would be a good idea to store the images in a database as then everything is more organized, easier to backup, easier to sort, i’ve never tried this on a released website, just some local test sites and was wondering what the fuss between the database vs filesystem was.
p.s. searching sitepoint for “images in databases vs filesystems” did not bring up anything so i thought i would ask around.
for a small shared site i would think that storing images in a database is actually better, as then you don’t run into the shared files problem.
Fixing the shared files problem without using a BLOB only requires one line of code – after you run the INSERT query to put the rest of the image’s details in the database, you use the generated ID of that row in the move_uploaded_file call to prepend it to the file name before saving it.
At any kind of scale these days, image hosting tends to be offloaded to a cloud hosting provider like Amazon S3. Not only does it cost less, scale infinitely and serve images faster, but it frees up your CPU and disk IO for scaling the website and not serving images.
on a shared server when uploading files onto it using php, $_FILES and move_uploaded_file, can be very problematic and is usually solved by setting the folder permission levels much lower (even 777) which is a problem considering that everyone on the same shared server has access to the folder now.
or at least this is what i was told.
At any kind of scale these days, image hosting tends to be offloaded to a cloud hosting provider like Amazon S3. Not only does it cost less, scale infinitely and serve images faster, but it frees up your CPU and disk IO for scaling the website and not serving images.
Now this is Interesting!!
i did not know of such a thing!
(just shows that self-learning always leaves gaps in knowledge here and there.)
and how does my server (php) upload files there? and what about backups?
any more details on that?
You send it to them with an HTTP request, just like the user uploaded the file to you (except programatically, rather than with a browser). There’s sample code and complete libraries you can download to use or learn from. Everything is well documented on Amazon’s site:
http://aws.amazon.com/s3/
thanks Dan, this does seem like the better option for bulk image storage
the “You send it to them with an HTTP request” really cleared things up for me.
one last question though, do you have any personal experience with it?
(and its privacy/reliability)
Most of my sites run on the Amazon Elastic Compute Cloud (EC2), and I use Amazon Elastic Block Storage (EBS) for disk space. They have been as reliable as any physical servers of my own for the most part. I don’t use Amazon S3 for anything but storing backups, which it works fine for. Access to S3 files is controlled in a pretty fine grained way, you can set access control lists with timeouts and private tokens for individual files… it takes some programming work to integrate into a site, but it’ll do what it says it does after that. You won’t have to worry about storage or performance of static file serving any longer.