binary retrieval speed debate

Anon

I have about three-thousand images (each about 200kb in size) that I want to search and retrieve. Is is faster to store them as binary ojbects in MySQL or to keep them in the local file system and call them when needed.

I haven't found much info that discusses this...any thoughts would be helpful.

Thanks,
plasma

brandonschnell

my vote is on the local file system and call them when necessary. i'm saying that after completing a project going the other route and storing them in mysql. it just isn't worth it. the only advantage to storing images in mysql is easy of setting permissions to the files. lots of decent reasons to store them on the file system as other posts have listed.

Anon

How could searching for a specific image file among thousands be slower when stored in an indexed database rather than looking for a single image file in the midst of several thousand other files in one massive directory?

curious and grateful,
plasma

[deleted]

The filesystem could well be faster because it only searches throught the filenames, leaving the filedata itself alone.

MySQL stores all blob data in the same file as the rest of the table data.

So if you store 800MB worth of images in a table, you get an 800MB file.
Searching in this huge table will be quite fast due to the index like you mentioned, but once the row has been found in the index, the database engine has to look in that huge clunky 800MB file to find the piece it needs.
You'll soon find that databases engines like MySQL are not very good at doing that. And you don't want to think about what happens in case of a sequencial scan... ouch..

This is why the most common combination of MySQL and binary data is to store the binary data in a file on disk, and store the location of that file along with some other data in the database.
That way you get the easy searching through the database, and the speedy file access from the filesystem.

brandonschnell

1) you lose the benefit of having the web server caching the file

2) database perfer short fixed length rows.

3) images use lots and lots of pages in the db file thrashing the indexes & paging scheme

4) when accessing the file the system is: having to retrive the file from the database. recreate the file. send the file data using the mysql connection to php. stored in a massive $var in php and echoed out to apache. THEN its sent to the user.

contrast that to apache which just has to read a couple of K from the file system, send it out to the user and repeat. doesn't use nearly as much resources.

Anon

Thanks Vincent for your concise and well explained answer...much appreciated.

plasma

[deleted]

1) you mean the filesystem won't cache the file.

2) true speedwise.

3) how? indexes are only on the tables you want indexed, and pages are used for all data.

4) true.

brandonschnell

1) you mean the filesystem won't cache the file.

yes the file system won't cache it, but neither does mysql. with the file on the file system apache & proxy servers can cache commonly used images.

3) how? indexes are only on the tables you want indexed, and pages are used for all data.

after adding and removing images stored in the db it has the issue of fragmenting the pages and forces any indexes to be recalcuated. most databases will try to leave a target percentage free in pages which takes more disk space & cpu time as data is rearranged on pages to "optimize" the data. my point tho is having a large file split up and tossed into smaller "virtual files" (pages) doesn't do much to speed things up.

[deleted]

1) mysql also caches as many results as it can, knowing mysql it would probably try to cache the binaries too.

But filesystem cache is much smarter.

3) I think you over-estimate MySQL a little about optimization and page-use. (does mysql even use pages?)

Why would a database leave space free in a page?

brandonschnell

mysql can cache some results, however i doubt it would be terribly effective in caching binary data as the data still has to be sent out to php.

given a choice i'd prefer to have my system and application files loaded in file system cache and my images in apache's cache where i can control how much memory they use.

yeah, i might be over estimating mysql optimizations, but i keep on seeing references to pages in the manual:

"Add an option to periodically flush key pages for tables with delayed keys if they haven't been used in a while."

"The FLUSH TABLE is needed to ensure that the all active index pages is written to disk before you start the backup"

"If the index pages are not sorted, sort them."

and look at this page for why space is left on pages (general info):
http://www.mysql.com/doc/I/n/InnoDB_physical_structure.html