Storing images in database

Forum Moderators: open

Message Too Old, No Replies

Storing images in database

as blobs or as JPG files on server

adb64

7:36 am on Jul 22, 2008 (gmt 0)

I'm currently building a new website which will have a number of image galleries. Over time more and more galleries will be added.
What will be be the best way to store the image JPG files? Just as plain JPG files on the server in a directory for each gallery or as blobs in the MySQL database in the table I have for each gallery. What are the advantages/disadvantages of each method?

Now I use one table as index for all galleries holding a title, description, keyframe image name and image directory per gallery and for each gallery I have a table in which each row contains the names of the thumbnail and large image files and a caption.

Thanks,
Arjan

janharders

8:05 am on Jul 22, 2008 (gmt 0)

blobs in mysql:
pro: one backup, easier replication, easier data integrity

con: slow (and i mean slow -- you're using InnoDB, right?) because you have to have an extra script to show a picture ... process generation, many more db-queries, big database (backup takes forever!)

files on server:
pro: very fast (nothing is faster than the webserver just sending a file)
con: not one backup, replication needs more work, data integrity isn't that easy to establish.

LifeinAsia

3:26 pm on Jul 22, 2008 (gmt 0)

Other cons for storing direcly in DB:
- slow restore if you ever need to restore the DB from a backup.
- most likely you'll hit the maximum DB file size long before you can run out of disk space

[edited by: LifeinAsia at 3:28 pm (utc) on July 22, 2008]

Ocean10000

2:22 am on Jul 23, 2008 (gmt 0)

How many image Galleries will be added, and how big will the Galleries be? Will the site need to grow to the point where will be multiple web servers serving it up? Or is it for example for a simple gallery of pictures for family and friends?

adb64

9:22 am on Jul 23, 2008 (gmt 0)

Thanks everyone for the response!
It is a small website for a regional sporting club with about 100 members. So the number of visitors won't be high and the galleries will contain pictures of tournaments and events. Each with, I guess, from 10 to at most 100 pictures.
For me it is my fist attempt to make extensive use of a database and MySQL. In the past I mainly used flat files for storage.
For now I will go for the option to store the images on the server and store the filenames in the DB.