File Fun

[dracolytch]

Ok, I'm mulling over different techniques to solve a problem, and
thought that insight from other PHP/MySQL gurus would be of value.

I'm weighing and experimenting with storing data in a MySQL database
instead of my usual file "cache". My normal technique for doing this
kind of interaction is to rename/store the file on a portion of the
server somewhere, and just store the path to the file in the DB.

In this particular case, I'm mostly working with images, and sometimes
it'd be nice to interact with them like they were data in the database,
though it's probably sometimes easier or more efficient to store them
on the server drive.

Securing the data is kind of a mixed bag... You can always store the
file in a non-servable portion of the drive and write a page to serve
it up to authorized users. This would require both a DB interaction and
a file read. If the data is in the DB, it'd save the file access (but
the data still has to be read from the HDD anyway). In either case, the
data read/serve is indirect (controlled by PHP).

The biggest advantage to storing the data in the database is
portability from one server to another. If the files are separate, you
have to deal with a mysql db, plus a bunch of other files, and make
sure the new system is pathed/secured correctly. If everything is part
of the DB, you really only have one file to worry about.

It's probably a lot more efficient to read the file directly off the
FS, instead out of a DB. I haven't seen any benchmarks showing HOW much
more efficient though. It's most likely significant.

I like the idea of being able to maintain all of my data in one place
(not having to do file system deletes/copies/etc). It also allows me to
do some pretty funky stuff like, run an update statement on my images
(in this case, not particularly practical, but it could be interesting
for working with things like evaluation routines in genetic
algorithms).

Are there pros or cons that I've missed? Has anyone actually TRIED it?
I've read about a 47kb limit problem that some people have. Anyone know
anything about this?

~D

[Mike Willbanks]

D,

Are there pros or cons that I've missed? Has anyone actually TRIED it?
I've read about a 47kb limit problem that some people have. Anyone know
anything about this?

Actually I am running a system where I store 3 different images in a
row. One large sizes, one medium sized, and one thumbnail.

Mine is a bit different. I cache all the queries and all pages on the
harddrive anyways. So therefore they are still in the database but get
pulled from a cache 98% of the time.

However with this technique I have found that it has been almost exactly
the same if not a bit faster than my previously tried routines.

When you read about it I think it all comes down to personal preference.
There are several ways to do it and advantages to both. I decided to
basically mix the two and have the full advantage :)

I also store files in the database as well, some disagree with this just
like images, but for the most part when they see the app they can't even
tell the difference. Most people are talking technically about what
should and shouldn't be done and why it would be bad. And just adopt it
when they hear it 400 times. Never hurts to try something once and
merge it to something else (just use oop so you can expand the original
class)

Mike