Storing images into a database – resolving a contentious matter

As with many other databases, MySQL provide a BLOB type that allows you to store binary data – images, wav files, videos etc. A frequent question developers have is regarding to storing images in the database. There is much discussion and argument with no final say on the issue. In one of my recent project the same issue was raised; the client and myself discussing the benefits and drawback of storing the images into a database. The project needed storing around 50,000 images, so it was important to get the question resolved satisfactorily.

After much deliberation we settled on using the file system. The major factor in the decision was that we needed the database and images decoupled as we would be having multiple databases using the same set of images. Also in the future it was possible that we would require some processing done on the images (cropping, resizing), which would be tedious and taxing if the images where stored in the database. So in light of these factors we found using a filesystem a suitable solution.

So should you store images in to a database?

That will depend on the actual purpose of the application, so the question is purely subjective and will have different answers depending on the situation.

Deciding where to store images is a matter of trade-offs. There are advantages and disadvantages regardless of whether you store images in the database or in the filesystem. The ever-so-useful MySQL Cookbook has the following to say on the topic.

1. Storing images in a database table bloats the table. With a lot of images, you’re more likely to approach any limits your operating system places on table size. On the other hand, if you store images in the filesystem, directory lookups may become slow. To avoid this, you may be able to implement some kind of hierarchical storage or use a filesystem that has good lookup performance for large directories (such as the Reiser filesystem that is available on Linux).

2. Using a database centralizes storage for images that are used across multiple web servers on different hosts. Images stored in the filesystem must be stored locally on the web server host. In a multiple-host situation, that means you must replicate the set of images to the filesystem of each host. If you store the images in MySQL, only one copy of the images is required because each web server can get the images from the same database server.

3. When images are stored in the filesystem, they constitute in essence a foreign key. Image manipulation requires two operations: one in the database and one in the filesystem. This in turn means that if you require transactional behavior, it’s more difficult to implementnot only do you have two operations, but they take place in different domains. Storing images in the database is simpler because adding, updating, or removing an image requires only a single row operation. It becomes unnecessary to make sure the image table and the filesystem remain in synchrony.

4. It can be faster to serve images over the Web from the filesystem than from the database, because the web server itself opens the file, reads it, and writes it to the client. Images stored in the database must be read and written twice. First, the MySQL server reads the image from the database and writes it to your web script. Then the script reads the image and writes it to the client.

5. Images stored in the filesystem can be referred to directly in web pages by means of tag links that point to the image files. Images stored in MySQL must be served by a script that retrieves an image and sends it to the client. However, even if images are stored in the filesystem and accessible to the web server, you might still want to serve them through a script. This would be appropriate if you need to account for the number of times you serve each image (such as for banner ad displays where you charge customers by the number of ad impressions) or if you want to select an image at request time (such as when you pick an ad at random).

6. If you store images in the database, you need to use a data type such as a BLOB. This is a variable length type, so the table itself will have variable-length rows. For the MyISAM storage engine, operations on fixed-length rows are often quicker, so you may gain some table lookup speed by storing images in the filesystem and using fixed-length types for the columns in the image table.



7 thoughts on “Storing images into a database – resolving a contentious matter

  1. You are forgetting the biggest advantage of storing images in a database: ease of backup. Compare that to images on a file system (possibly spread over several servers).

    If you do store images in a database, they should be in their own table (just id, the image, and possible some other minor information such as type or size).

  2. i think storing image (or any file) in DB:
    * is suitable for light-weight file or low record number
    * is a nice way when we need to encrypt content of the image/file for protection
    * is worst choice otherwise :)

  3. At 2nd point – In a multiple-host situation, that means you must replicate the set of images to the filesystem of each host. If you store the images in MySQL, only one copy of the images is required because each web server can get the images from the same database server.

    How about that database require cluster (many database => many image sets, you will have same situation with filesystem.

Leave a Reply

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

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>