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.

This site is a digital habitat of Sameer Borate, a freelance web developer working in PHP, MySQL and WordPress. I also provide web scraping services, website design and development and integration of various Open Source API's. Contact me at metapix[at]gmail.com for any new project requirements and price quotes.

6 Responses

1

Svein Tjonndal

February 21st, 2013 at 11:47 am

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

Marco Janssen

February 21st, 2013 at 1:52 pm

Why isn’t a NoSQL database considered like MongoDB?

3

علیرضا معظمی

February 23rd, 2013 at 7:44 am

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 :)

4

Wil Moore III

February 24th, 2013 at 2:55 pm

You might want to check out MongoDB/GridFS:
http://docs.mongodb.org/manual/applications/gridfs/

Also, S3+CloudFront is great for this if the price fits for you.

5

pealsaidisk

April 9th, 2013 at 8:47 pm

An fascinating discussion is worth comment. I think that you should write much more on this subject, it may well not be a taboo subject but frequently consumers are not enough to speak on such topics. To the next. Cheers

real christian louboutin

6

Phat Nguyen

May 2nd, 2013 at 6:52 pm

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.

Your thoughts

Sign up for fresh content in your email