Databases and Images - Performance Question

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
User avatar
jwalsh
Forum Contributor
Posts: 202
Joined: Sat Jan 03, 2004 4:55 pm
Location: Cleveland, OH

Databases and Images - Performance Question

Post by jwalsh »

Hi,

With my current project, there is potential for some large volume of users uploading images to their account. I've thought about doing this 3 different ways, but which way would provide best performance?

1. Store in blob in MySQL
2. Upload File with random unique filename, and storing filename in DB.
3. Save the original filename in the DB, and save the file itself following the primary key.

Ex. original filename is james.jpg but is renamed to 5.jpg because it's ID is 5.

I'm leaning towards the DB, because it could keep robots from downloading all my images, but will I take a significant performance hit storing them in blobs?

Josh
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

keeping them in the database wouldn't keep a robot from downloading them, it'd just eat more processing time requirements.

2 & 3 are both good solutions, I'd only do 1 as a last resort.. (but I'd move to a better host before that ;))
Last edited by feyd on Thu Aug 18, 2005 7:27 pm, edited 1 time in total.
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

feyd, I am working with a Products_tbl where each product has an image.
Anyway, each image I upload to the server I create an entry in Images_tbl, image_id and image_path.
I assign the image_id to the product_id and retrieve the filepath to display the image.
It would be easier I think if all the images are recorded in the database and may be have a admin page which list all images and I think unlink($image_path) would delete it.

do you think its a good way of doing it??? :roll:
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

very often, storing images in a database requires:
  1. more time to fully load a page
  2. more memory consumption the database server has to deal with
  3. more memory consumption php has to deal with (database server has to load data into memory and transfer it into php)
  4. more space consumed by a backup
reasons for it:
  1. DBMS may be faster and more efficient at storing
  2. backing up the database backs up the images
  3. the data may be more secure, being outside the filesystem
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

thanks for your explanation.
I always store images as files in a server directory not in the database ofcourse they are RDBMS not multimedia databases.
I create only entries in the database for management.
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

feyd wrote: reasons for it:
  1. DBMS may be faster and more efficient at storing
  2. backing up the database backs up the images
  3. the data may be more secure, being outside the filesystem
One other reason, but its quite rare:

You have a User Defined Type in your database to store images that lets you do image-level comparisons in the Database.

For instance in PostGreSQL and many commercial DBMS's you can define an "Image" type, and you can define a "sameness" measure that lets you search for images that are similiar to each other, using efficient (typically C code) in the DB. or in some GIS systems you can have it "find overlapping regions" for mapping, etc
User avatar
jwalsh
Forum Contributor
Posts: 202
Joined: Sat Jan 03, 2004 4:55 pm
Location: Cleveland, OH

Post by jwalsh »

Thanks everyone :)
Post Reply