Advice on storing images in mysql db..

Anon

Hi,

I've set-up a script to upload images to my server then resize and insert a thumbnail image and the original image in one table which has three fields (imgid int,thumimg BLOB, fullimg BLO😎 I was then going to store other image information in another table (like name,description,date).

My question is, will this affect the speed of the database or create any performance issues? (max image size is 100k in jpeg format, wouldn't be more that 500 queries a day I expect)

The main reason I want to store them in a db is just because they are easier to keep track of and easier to backup/download from the server.

Thanks,
Matt

[deleted]

My advice: don't store images in a database.
Why not? because all you'd be doing is use the database as harddisk space.
There's no database functionality that you can use on binary data, you can't sort it or anything, so why put it in a database?
Your table will also grow quite a bit (100k doesn't sound like a lot, but times 10 is already one megabyte, times 1000 is a G😎 which will slow them down.
If you do some benchmark tests I think you'll find that loading images from disk is much faster than from a database (unless you have a monster of a database server)

So instead, store the image on disk, and only store the location of the image in the database. Locations can be searched, ordered etc.

As for backups, TAR is not that difficult is it? :-)

Anon

Thanks, I'll store them in a directory :o)

shivers

Hang on, it's not quite as simple as that.

Firstly - there are some database things that can need to be done with images - or at least image related data (e.g. recording the people in photos and then doing lookups by userid). In this case it is significantly easier to store the images in the database and avoid all the table<->directory mapping problems.

To avoid (partially at least) the speed issues, store image data like title, caption, etc in a separate table to the image data itself. for that matter keep the thumbnails and full size images in separate tables as well. Only have the binary image data (and a Content-Type: field) in the table itself. Use a numeric ID with an index over it to access the data. Works fine and is blindingly fast 😉

Of course, web servers are better at caching disk data than database stuff so for high loads this isn't good - but for most things its fine, and the clearer, tidier design makes up for it.

[deleted]

Discussion time! :-)

1) Like I said, you never do any kind of database operations on the image itself, only on information related to the image, so there is absolutely no need to store the image itself in the database.

2) Fast but still not as fast as direct filesystem access. Try sticking 200 images of 100k each in a MySQL table and see how fast it is 🙂
The point is, you can't directly acces an image in a database.

What happens is that you first lookup which image you want.
Then in the client's page you print a link to a php script that can fetch the image.
The clients browser calls that script, your server runs the script,
requests the data from the database server, who then does a search and returns the image data to the php script, which will then send it on to the browser.

In the other scenario you lookup which image you want, and print a link to the image in the clients page.
The client browser requests the image from your webserver, who opens the image and sends it to the client.

See the difference? :-)

3) It's easier to understand, but I wouldn't call it tidy just yet. Don't forget you need extra routines to upload images into the database and extra scripts to get them back out. You can't use normal filesystem commands to get them in, and you can use normal HTTP to get them out.

shivers

1)
fair point.

2)
I'm already working with more than that - ~1000 images ranging between 50kb and 150kb. virtually instantaneous lookup + download on a moderately loaded, average spec server. (RH Linux 7 + Apache + PHP3 + mySQL on a K6-2 500 w/64Mb).

I am aware of what happens internally. I still prefer putting them in the db. see below:

3)
the scripts to upload/download the images are only about 5 lines apiece - including tidy error handling.

I prefer the organisational aspect of it - the only actual files are the static site images and the PHP scripts. The database holds all of the data.

I know there is a performance hit (as I mentioned before) - but even for large amounts of data its not noticable until you start getting large amounts of traffic on the site.