Topic awaiting preservation: Blob vs filepath (Page 1 of 1)

Could anyone tell me the relative advantages/disadvantages of storing binary data directly in MySQL instead of puting the files in a directory and storing a file path in the database?

I've heard that the queries could take a serious performance hit with lots of blobs stored in the DB, but I don't know why that should be the case.

/* Sure, go ahead and code in your fancy IDE. Just remember: it's all fun and games until someone puts an $i out */

I can't really say in regard to db/file, but everything I have read has also put excessive blob use in a bad light.

The better option seems to be using a text, or a longtext field instead. I can't verify this as valid info, but I've seen it written many times in mnay places...

I can't back this up from a performance/tech way, but common sense tells me that if I don't have a very specific reason for it, large chunks of binary data belongs in a filesystem while the pointers (paths) and metadata for them belongs in the database.

As for the queries, don't know this for sure, but say you do a search on a database where you have one column called "data" as a blob, and in there you have some rows with text and others with binary (images and such), then you do the search "... WHERE data = '%yellow%'..." I suspect the db will actally try to search inside the binary data as well as inside the text which wouldn't exactly make it faster...

At least this is what I think, I'm not a db-guru though.
/Dan

{cell 260}
-{ a vibration is a movement that doesn't know which way to go }-

Well - I have experienced first hand that storing images in a database is a sure way to make your site dog slow - We're talking a maximum of 6kbyte/second output here from a webhost that usually deliveres 85kbyte/second easily from files.