Database storage engine for images
My application uses a database for (among other things) storing scanned documents. These are usually JPEG images, and mostly just text.
I know it's recommended to use files for images and link to them from the database, but it's just easier to retrieve them this way. The application uses one single server and multiple client开发者_如何转开发s, and storing files would waste a lot of hard drive space (overhead, sector allocations, etc.) and require the use of shared folders and mapped drives and stuff. Slow, and less secure.
Anyway, some of our customers have been using our application for a few years, and their databases have grown into the tens of gigabytes, mostly because of these images. At the moment they're stored in an InnoDB table (MySQL).
Question is: is there a better way?
The data itself is write-once, and not normally deleted (but theoretically possible), so something that is slow to store (within reason), unchangeable and fast to access would be perfect.
I'm thinking of making my own storage engine that would include compression, indexing, and caching that only allows two columns (bigint, blob) per table. Does something like this already exist?
MSSQL 2005 introduced file-streaming which allows you to store pointers in the database, but keep the blobs out of the database and in a directory (as they should be). The neat thing is that when you backup the database, the blobs get backed up as well. Best of both worlds. The directories are restricted access as well, typically only SQL itself gets access.
Store the images as files, keep the pointers in the database.
精彩评论