[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql
Subject:    Re: images in MySQL db
From:       James Lyon <james.lyon () aztec ! co ! uk>
Date:       2000-05-31 16:43:46
[Download RAW message or body]

> } I've seen this topic discussed several times on the MySQL mailing list
> } (hooray for MySQL) and the received wisdom is that it is a
> } much-debated and unresolved issue.
>
> It's the last reason above that frustrates me - "unresolved"... :(

Put it this way ... I decided that for my application that the overhead in
maintaining the loose image files was less than the task of figuring out how
to store them in the DB -- and I let that decide it for me!

In future I will get around to the DB solution and try it and then I will
always do that until I see a performance hit.

That, at least, is how I've resolved it for myself!


> you don't, folks can point their w3 browser [assuming a w3 -> db
> environment here] to a certain image, rather than go to a php page that
> grabs that image out of the db.

Corretcamungo ... although you could write a PHP wrapper to hide the directory
where they were stored -- but then it'd just as easy to put them in the DB
instead!


> This has the affect that if one has info [or banners, yuck] on their
> pages, then surfers won't see that info if they access the image directly.

Sure ... this hasn't mattered to me in the past, hence not bothering about it.
I do prevent viewing the directory where my images are stored though ... so
people can't trivially leaf through all my pics. Not that I really care.


> Of course, the major downsides to this are the fact that one can't just
> edit /home/images/image1.jpg and have the change immediately be felt in
> MySQL if one is blobbing the image into the db. I might want to batch
> process a few thousand photos [add a tagline, or watermark], and that is
> much easier if the images are stored in a flat filesystem.

You could easily write a PHP script behind a user/password (in a private
admin. section of the site) that took images from a non-web-viewable directory
and hefted them into the DB. That way you can batch process into a filing
system location, then batch them into the DB after they're all ready and
prepared for storage.


> But what about performance? If the average jpeg is 200k [there are smaller
> thumbnails], and I have 6-7 thousand images, will MySQL be on its knees
> every time it's feeding a user an image? I would appreciate any further
> enlightenment and/or pros/cons to the "blob vs. file name" debate...

I personally haven't tried it, but the overwhelming impression I get is that
MySQL can handle this! Even on 32-bit machines, MySQL tables can easily be 2GB
-- on 64-bit OS, MySQL is about to be released (or has been) that supports
many millions of terabytes per table. For the time being 2GB allows for 10,000
images at 200k per image average. To be honest, I can only recommend testing
it -- your OS and hardware will no doubt play enough of a role in the final
decision that someone-else's experience may not prove it either way for you.

I'm guessing a bit here!


>   } If this thread continues can I suggest we do it off-list as it's not
>   } really a PHP issue?
>
>   Sure thing [Subject changed as well]

I've cross-posted to the MySQL list in case anyone wants to comment there.


Cheers,
James.



-- 
---------------------------------------------------------------------
Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
posting. To request this thread, e-mail mysql-thread38842@lists.mysql.com

To unsubscribe, send a message to:
    <mysql-unsubscribe-mysql=progressive-comp.com@lists.mysql.com>

[prev in list] [next in list] [prev in thread] [next in thread]