I have a site driven by a cms script that I wrote in php. For convenience, I have stored all the images in a mysql database. This makes it quite simple, scriptwise, to retrieve the image data from the image table, size it as I want it and insert it in an <img> tag in the page. Another way I could have done this is to store the images in a directory and store the location of each image in any record holding the page data on which that image appears. It is slightly more complex, scriptwise, in this case, to resize the image as desired on the page.
Can anyone tell me if one way or the other is preferable and why? If someone has a another idea of how best to store images on a server, I’d be interested in hearing about it.
I will recommend to use file storage instead of mysql. Because when there are more product images on search results page, there will be more calls to your mysql server, this might be problem. When more users try to use search results at same time.
Well, no, there wont be, because you’d retreive all of the image entries in a single query, so there wouldnt be more calls. It’d be a thicker call, but not more.
As far as the OP’s question - there’s some debate on the issue, but for my money I would never store image data in a mysql table, especially if that database is not running on localhost. The transmission size of the result query if you ask for… 50 entries would be heavy (depending on image size, of course), and would slightly slow down your script. Retrieving the file path and using that to pull the images will be less strain on the script.
It’s also easier to backup the images when they’re stored in the file system, if they’re stored in the db then the file size of any backup files will greatly increased. If say your storing photos and say each photo is about 5MB in size. If you have 200 photos then that is going to be around 1GB in size. You may find that even though PHP and MySQL are located on a server in the same data centre they may not actually be on the same physical server. PHP may be on a server optimised for data processing and MySQL on a server optimised for data storage.
You can connect to more than one DB at a time. So you can keep your photos in separate databases. Which will give you more control over the size of each DB. This will also benefit backups. Multiple smaller backups, one of each DB. More reliability if you keep each backup on a separate media carrier. Faster restore if only 1 or 2 DB’ses get corrupted.
Also, you hide the location of the originals by keeping them in the DB. This is extremely important if someone starts leeching/scraping your images with a program like Offline Explorer. It will eat up your bandwidth in no time, and possibly strain your server.
By hiding your originals, you only make available the resized/smaller images, to which you could also add a little bit of noise while resizing. If your server power permits.
Actually, this is the way I am doing it now. I have all images in their own table. This, I presume, is as good as having them in an entirely separate databse, or is it?
I give people a chance to size the image before uploading it, since the images will be used on a web page where they are unlikely to be any larger than a portion of a page and this save some space in the table. Then, they can either size them again when loading them onto a page or trim them with size attributes in the image file. This doesn’t create much download overhead as they are already stored as a reasonable size and I am only download a couple of images per page.
I have some browse pages for the admin user, where they can display all the images as thumbnails and see a larger version if they want as they are editing the site and deciding what images to display where.
Depends on the maximum size allowed of one database.
Approaches:
a) Many separate image tables in one database, means total storage for all image tables is up to the maximum size of one database.
b) Many separate databases, one for each image table, means that each image table can grow up to the maximum size of one database.
With a) you have to watch the maximum database size. Once it’s reached you have to continue in a new database or purge and compress.
With b) you will have to take into account the maximum number of databases allowed, and the number of simultaneous database connections allowed (sql CONNECT statement).
For creating and restoring backups it doesn’t really matter. Backing up each image table separately in approach a), or backup up one database which contains only one image table in approach b). Think before making one monolith backup in case of approach a). Most of the time it’s counterproductive.
I assumed that you were the only one uploading images, and probably in high quality as well, to make them available.
If the images are also uploaded by others, and the image sizes are reasonable, you should be OK then.
If your hosting has a bandwidth limit, don’t become a victim of image leechers.