Storing images in Databases

Good or bad idea?

[Mark Barrett]

Please can anyone comment on the pros/cons of storing images in a database.

I have a MySQL table and every record has an associated .jpg. They are quite small - each one between 4-6KB. At the moment they are stored in a remote directory.

But I believe I can set a record type in the MySQL table to BLOB and store the images there.

From a "neatness" point of view I like the idea of doing that but my manual is not exactly enthusiatic about it - "You would need to write your application to handle images as binary data - so although it IS possible you may well find it easier etc. etc."

Can anyone offer any advice or guidance please.

Mark Barrett

[mcibor]

Unless you have more than 10 000 small files it is wiser to store them on hard disk, and in db just url to them. Otherwise it would be really hard to show them to user.

If you use windows then it can be a good idea to store them in category based folders. If Linux then it doesn't matter so much.

Hope this will help you to decide
Michal Cibor

PS. Think how would you get the data to show it to user, as you can't just write: <img src="img.jpg">.

[arran]

Mark,

For simplicity and performance reasons I would just store the image path. Your web server will be better at caching than MySQL.

[mincklerstraat]

Files much better. Otherwise, for each image you want to display, a separate connection has to be opened to your database. Even if it's only one image per page, doubling the connections to the db isn't fun to think about. Think about it. Each time you do <img src="image.php?imageid=345">, that's a separate request to the server, with php going to drege up that image from the db. Even if you do use scripting to grab images from a non-public branch of your filetree, just using readfile() creates very, very little overhead, while hitting the db creates a lot. Just connecting to the db alone, without any queries, is something which already takes a significant amount of server time (time more in the sense of processing cycles than actual time).

If you just have the image location saved in the same table as other information belonging on that page, the extra overhead is amost 0. If it's a simple JOIN query, it's still peanuts compared to a separate db connection.

[Mark Barrett]

Thank you very much for these comments.

I understand now where my manual was coming from. Most definitely I will leave things as they are.

Thanks

Mark