Storing images: database vs. file system

A question from comp.lang.php:

I would like to know what the differences are in terms of security, reliability and resources between storing user-uploaded images in a file or storing them in a mySQL database.

The only significant difference is the DB server load. Since <img data=”*”> tags are not (yet?) commonly supported by browsers, you need a separate instance of an image display script (and a separate connection to the DB server) to display each image. So if your Web page has 100 images on it, it will require 101 nearly simultaneous connections to display itself and the images, as opposed to one connection if images were stored in the file system. Granted, image retrieval connections would be very short, but at high loads, this architecture would be patently inferior to the disk-based alternative.

Also, if you use MyISAM tables, storing large images in them will bring you to the limit of table file size much more quickly. If your average image is 1 MB and your file system’s maximum file size is 2 GB (there are still some older Linux servers whose file systems have this constraint), you will only be able to store about 2,000 images until your table exceeds the maximum allowed file size.

Additionally, you’ll need to make sure that your MySQL server is configured with a packet size large enough to transmit the entire record, including the image. Up to MySQL 3.23, this limit was 16MB for MyISAM tables. As of MySQL 4.0, situation changed; the effective maximum length of LONGBLOB is determined by maximum packet size and available memory.

Our company is starting an image-competition soon, and I am not sure if I should write the php script to insert the binary code into a mySQL database or if I should just store the files in a dedicated folder and the data about them in the mySQL database.

Large image sharing sites invariably stick to the second approach. Moreover, images may be stored on separate servers, optimized for serving static content.

I answered a similar question here a few months ago, take a look

Which system should I use?

I usually advocate the use of file system.

Is there a real difference between the two?

Only at high loads…

Leave a Reply

Your email address will not be published. Required fields are marked *