Storing image data in a MySQL database?

Question

I am implementing a project that deals with a significant amount of images.

In your opinion what are the cons/pros of the following two approaches:

• I need to store thousands of items, each item as several string properties and an image.
• Each item as an ID (integer)
• MyISAM tables
• How would you store the images:
  • approach 1: store images into a directory and each image named as ID.jpg
  • approach 2: store images into the database as a binary BLOB

Using approach 1 I can access the image directly and that's it

<img src="same_directory/10.jpg" />  

Using approach 2, I can still use the above HTML, but need to redirect that jpg access to a PHP script which will return the real image from the DB.

In terms of performance which one do you think its faster?

I am keen to approach 1.

Answer 1

advantages of approach 1:

• Retrieving the flat file form webserver is more faster.
• most of the web hosts likely to follow this approach.
• the file system is faster for flat file storage.

advantages of approach 2:

• All your data is kept in one place, if you migrate your website/database the images will just be there
• Its easier to sort/delete/etc...
• Since you have to serve it via a PHP script, you can perform additional things such as security if required, or image processing (obviously you can do this with flat file too, but you have to make sure the security cant be bypassed by leaving the images in a public directory).

considering performance approach 1 is best to proceed.

Answer 2

Storing on filesystem is faster.

Answer 3

I'm be tempted to use the first approach as there's no real value in cluttering up the database with image data. (Fetching the data from the database will also be significantly slower than simply loading it off disk.)

However, as an suggestion you might not want to store the full path on disk to the image in the database table, to aid portability in the future. (i.e.: Just store the portion of the path and filename off a 'known' base folder.)