File Size/Speed Optimisation

mlimburg

Howdy,

Serious question here. I'm about to start development of a large webapp, using a MySQL backend. It will consist of approx. 1 (and up) million records. Each of these records will have a number of relational records, including base data, but also a variable number of images.

My question is, is it worthwhile placing these into a well designed database/table structure, or configuring them within the file structure?

My specific interests are:

filesize; are images stored with a smaller data size within a table, than sitting on the file tree
speed of access; would it be faster to pull the images from the disk as a file, rather than from a seperate MySQL table

TIA

Anon

I think that putting images as part of the database will slow it down. May be you should just place a location of an image on a server.

Let's say your root directory is /www so you could place images in /www/images, and then you just place a link to an image into a database (ex. of a image link /www/images/image1.jpg.)

So if when you retrieve records you can place image link in between <img src=$img_path> where image path is /www/images/image1.jpg

Your database should work quicker, but.. overhead is the maintatnce of the images in the image directory.

I've built one of the search engines in this fashion. Instead of place data directly into the talble field - I just placed a link.

Hope that helps you somewhat,

kparker

You're on the right track to start with, but:

you could place images in /www/images

you would only want to do this if your intention was to absolutely kill performance. Remember, they're expecting upwards of a million records--do you have any idea what kind of performance you'll get just trying to open a file with 1,000,000 files all in one directory?

Instead, you need to distribute the files into a larger number of directories. To do this right, you really need to know what the expected filenames are, so you end up distributing them somewhat evenly. Let's assume that the files are just going to have names consisting of random 16-char-long hex digit strings. Then, you could create a set of top-level directories:

  /www/images/0
  /www/images/1
  ...
  /www/images/f

and distribute the files into directories based on the first char of the filename. But since, this really only gives a 16-fold speedup, and we're talking millions, then extend each directory to a set of subdirs based on the second char, and so on, until you end up with only a thousand files or so per directory.

Anon

Great point!

However, the issue I addressed was whether it is better to store images in a database. Also he said that they have variable amount of images.

Don't get me wrong - you make a good point. My example was a concept, an implementation is up to a user.

Implementation wise I'd set up a RAID consisiting of 10,000 rpm scuzzies. Makes no sense to store that much data on one hard drive.