[n8kindt]

the previous programmer for the html/php site i now have the reins of used a mysql database, but for SOME reason he decided to post product images to a file directory--rather than store it in a BLOB. i haven't had ANY experience with BLOBs so i'm wondering if he knew something that i didn't. the only problem i could possibly foresee is that we watermark our images. but the original photo is NOT watermarked--it is done in realtime by another page that hands the photos out on request.

so, is there any reason i shouldn't be using BLOBs to store images? currently, a problem that has come up quite frequently is when we rename an item code, that the function he created to rename the image is not working. now, i have no idea why he didn't assign image names based on the autonumber id (which would never change) so i'm just wondering if he really didn't put too much thought into this area. what do you guys think?

[coolsti]

Others may have different views, but in my applications, I keep very large data items, like images, as files in directories, and just store the links to these in the database.

It is a matter of efficiency. Images can be very large, and very variable in size. A database table that holds these can then become very large, and unless Mysql has some internal way of storing blobs in a very intelligent way, the changing of an image (updating it) would seem in my mind to be very inefficient.

Storing a file link in the database is on the other hand leading to relatively small tables. As long as someone is updating the image file and not its location or name, then the file link stored in the database will remain valid. But if for some reason people start renaming their images or moving them around on the hardisk, then you need to make sure the database is correspondingly updated.

On the other hand, if you use a database as the only way to access your files via file links stored in the database, then you can just dump all your files into one directory someplace, you can name them whatever you wish, because you can just find them via your database application.

[n8kindt]

Originally Posted by coolsti

It is a matter of efficiency. Images can be very large, and very variable in size. A database table that holds these can then become very large, and unless Mysql has some internal way of storing blobs in a very intelligent way, the changing of an image (updating it) would seem in my mind to be very inefficient.

Storing a file link in the database is on the other hand leading to relatively small tables. As long as someone is updating the image file and not its location or name, then the file link stored in the database will remain valid. But if for some reason people start renaming their images or moving them around on the hardisk, then you need to make sure the database is correspondingly updated.

yeah, i think the biggest fallacy on the previous programmer's part was linking the image names with a field within the database that the user has access to change. i don't think i would even be asking this question if that wasn't the case.

i'm wondering, if i put the images in one table, and the images in another table, this should not affect the performance of the database unless an inner join is used, correct?

[Atli]

Hi.

Storing images inside the database is really just a matter of security and convenience. It is easier to control images inside your database simply because you have complete control over them. Storing them in an external directory requires you to interact with the OS's file-system, which gives the OS a measure of control over them.

On the other hand, storing them inside the database can cause other problems. If they are stored inside a frequently accessed table, it will slow the queries down considerably. Images are large and can cause large differences in row size, which slows down queries.
A easy way to avoid that is to store images in a separate table and avoid joining that table in queries.
(Same goes for Text fields and other variable-length fields)