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
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
Comment