MySQL/Images/DB or not DB

I would like to find a (nearly) definitive answer to the age old question of storing images/documents in a database.

I have built a (workable !) document imaging application that uses MySQL for the backend. Documents are stored internally in MySql using two tables. The first stores summary information about a document (size,type,date,name etc) with the second storing 64k chunks of the actual image or document (think inodes). The second table is foreign-keyed to the first via an ID column.

Documents are typically scanned letters or project files which are delivered to us on CD as compressed multi-page tiffs (FAX type format). Each compressed page is usually 10-50k.

I have built an image loader, which reads images from the CD, converts them to single-page (compressed) PNGs & stores them into the DB. I use an ASP/IIS-based solution for viewing the documents.

This is all working reasonably well and we now have c100,000 pages stored. However, this application currently runs on the intranet and I am looking at linking it to an external web application. This obviously raises various questions about efficiency, hence my revisiting the issue of DB storage.

Incidentally, we are expecting the system to grow by about 5,000 pages a month.

Most people on this forum seem to favour storing the images on the filesystem, with a reference stored in the DB, due to the efficiency of the OS and/or webserver. But is this true when you are dealing with 100k+ images ?

Why wouldn't MySQL will have been optimized to read it's own table data ? when you add in ASP/IIS and any folder mapping to the equation, can we realise massive efficiencies ? will MySQL be more efficient (disk-space-wise) at storage (MySQL table will be one LARGE file, with minimal block wastage, whereas each file will incur disk block wastage)

Any thoughts anyone has on this issue would be most welcome.

I'd check out what (if anything) mysql.com has to say about efficiency of using mysql to store images. I've always heard that it's more efficient to store images on the filesystem. How much it really matters I can't really say. Half the appeal to me of using the filesystem is that I don't have to worry about sending content headers for most images as I would if serving up images out of the database. This is of course a concern primarily because most of my image management is the placement of images within Web pages, which is somewhat different from what you're doing. If inodes/blocks are a big concern, then perhaps storing images in the database is what'll work best for your particular case. Regarding efficiency, while mysql may be fairly well optimized to read its own table data, you have to keep in mind that you're essentially forcing a double read of the image, from the database to a buffer/variable and then from there to the browser (particularly with the chunking you're doing). If you go straight from the filesystem, there's a single read with only the tiniest file I/O to spit the image out.

Thanks for your response.

Incidentally, I solve the content header problem with a DocType table which maps a tinyint in the document table to a MIME type/content header string.

Having conducted (a little) research on commercial imaging systems, it appears that they all store documents in a DB.

I think the main concern there is isolation and disk block wastage. If most of my images are 10 to 50k then, on a disk system with 64k blocks, I will lose 54 to 14k per file stored but the mysql table will in total waste a maximum of 64k.

Can anyone elaborate on the double-read problem. My understanding is that there will be one disk hit in both cases, the rest is memory moves and socket writes.

Does MySQL have the lead as it will already have a handle to the DB table's file ? In my situation, the same image/set of images will rarely (if ever) be viewed more than once, so will any web server file caching be of use to me ?

I may be off target on the double read. I was thinking possibly that the images would have to come from the db into memory upon query execution and then be dumped back out to the user, whereas a file system call it seems to me perhaps sends straight out to the user without having to be stored in a variable in memory and then output. I'm by no means confident about that supposition, though, and you seem to have done your homework on systems issues, so I'll defer to your speculation on this point.

Yes, for your purposes, the mime types aren't really a problem. My point was that for the type of day-to-day work most of us are doing, to have to bother to print out content headers for inline image display within HTML output would be to do the work that a perfectly good Web server is sitting there just itching to do. So storing catalogue images or personals photos or nav images in the database just makes for more complex, unnecessary code. Your implementation of headers seems reasonable, though, given what you're doing.

I imagine commercial imaging systems store images in a db partially because by doing so, especially if they ship with their own db, they're not at the mercy of the file system structure, permissions, etc. There may be other very good reasons for doing this as well, performance, disk block waste, etc., among them.

Really, I don't know that it can be said that there's a definitive right way to store images. It probably depends on the given circumstances. It seems as if you're probably justified in (and in fact stand to benefit from) storing images in the database rather than on the filesystem. Obviously, that doesn't mean that all apps would be so justified.

If you do any benchmarking (which probably wouldn't be all that hard to put together), I'd be interested in learning what you find out.

Has anyone got any current opinions on this??


What performance do you lose from using a db?

I assume maintaining images in the db would be easier - with reference to backup and deletion (deletion could be handled by a CASCADE, as opposed to within the code.


Cheers for your comments,

Ben