On Wed, February 28, 2007 4:04 pm, Mark wrote: > Kevin Waterson wrote: > >> This one time, at band camp, zerof <zerof@xxxxxxxxxxxx> wrote: >> >>> It is not a good practice to store pictures in DataBases, use >>> links, >>> instead of. >> >> Rubbish, where are your benchmarks? > > It has almost nothing to do with benchmarks. > > Images are typically best supported in the form of files. They are > more > easily manipulated by external tools. There are definitely far more issues than performance to this question. I personally prefer to store images OUTSIDE the DB, for several reasons. > The web browser sees an image as a single HTTP request. Invoking the > PHP > script engine, parsing the script, and executing a SQL query to > retrieve > the image from the database is less efficient than letting the web > server > just send the file. Even this is not ALWAYS true. If the image is tiny enough (think an icon), and the other data that you HAVE to fetch on the page is large enough, then the image itself may be small enough to be cheaper to pull through the DB rather than an extra fstat. This is an edge case in a niche situation, but it's there, and there's a benchmark in the archives proving it. > Image files do not need to be constrained by the rigid requirements of > a > relational database. Unless you find that tracking down the image in the file system is more trouble than letting the DB nuke it if you have frequent record deletes. This could be particularly true in some weird case (again) where there are many inter-dependent records, where a simple trigger in a DB would nuke the file when the last reference goes away, but you'd have to write some gnarly query in your cleanup script to decide if you can remove the file system image or not. Again, it's an edge case, but there can be no question that for somebody somewhere, this is clearly a Better Way. [Not for me, and not for most newbies, but it's there.] > I could go on, but it should be clear enough that putting images in a > database is not a good idea. Add the word "usually" and I'd agree with you. Without it, you're just plain wrong, even if you are Mr. Mcache. :-) [right?] One also has to consider your backup strategy. Throwing images in the db makes backing them up easy, but quite expensive, as you'll probably be dumping them out all the time, and then archiving the dump, and using a LOT of backup space for something that, in MOST applications, changes seldom, if ever. Backing up the more or less static images (for MOST web apps) can be done more efficiently with a separate process, in MOST cases. It might require a quick check of the last few records and images added to be sure that there wasn't a hiccup that orphaned record/image, but that's probably a Good Idea if something went wrong, really, even if you mostly trust your DB with images to have done the right thing in a crash... Further, in the unlikely event of a database/hard-drive disaster, with backups also possibly lost/broken/whatever, re-building the DB without images will PROBABLY be less painful than the one with the images, assuming the whole thing isn't so corrupted, throughout all backups, as to be useless anyway. In other words, without the images in the DB, you're going to have an easier time restoring the data of the DB, and then dealing with any db-file-image relationships separately, than trying to restore a DB that's loaded up with large images. Finally. consider that the first easy major performance enhancement of a site that "grows" beyond the ability of hardware to handle it is to split the DB off to another box. Sucking down the image through the LAN from one box to another is going to hurt a lot more than adding a THIRD box, custom-tuned for images, or even going the Akami [et al] route. Of course, you may be running a site that is never going to have to worry about heavy traffic. I know I have lots of those. :-) Ultimately, this is a lot more complex than it first appears, and can only be answered correctly in the context of a specific web application and architecture, but I'm still sticking to my guns and saying that most newbies should be using the file system for images, until they have a really good reason to put them into the DB. -- Some people have a "gift" link here. Know what I want? I want you to buy a CD from some starving artist. http://cdbaby.com/browse/from/lynch Yeah, I get a buck. So? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
