Re: Difference between storing files on folder and in mysql db

On Jan 8, 8:05 pm, Jerry Stuckle <jstuck...@xxxxxxxxxxxxx> wrote:
NC wrote:

The only significant difference is the DB server load. Since <img
data="*"> tags are not (yet?) commonly supported by browsers, you need
a separate instance of an image display script (and a separate
connection to the DB server) to display each image. So if your Web
page has 100 images on it, it will require 101 nearly simultaneous
connections to display itself and the images, as opposed to one
connection if images were stored in the file system. Granted, image
retrieval connections would be very short, but at high loads, this
architecture would be patently inferior to disk-based alternative.

Not true. <img> tags are handled identically by the client, whether the
image comes from the database or the file system. The client doesn't
know or care if the image is from a database or not.

The client doesn't indeed, but the DB server does. Let's say we have
a script img.php (error handling omitted for brevity):

$id = (int) $_GET['id'];
mysql_connect('server', 'user', 'password');
mysql_select_db('images');
$result = mysql_query("SELECT imageData FROM images WHERE id = $id");
$record = mysql_fetch_row($result);
header('Content-type: image/jpeg');
echo record[0];

Let's further say that there is a script index.php that includes this
fragment:

for ($i = 1; $i <= 100; $i++) {
echo "<img src='img.php?id=$i'>";
}

So when index.php runs, it outputs 100 <img src='img.php?id=*'> tags,
so 100 instances of img.php are also run and each makes a connection
to the DB server.

Please feel free to point out any errors in the above argument.

Also, if you use MyISAM tables, storing large images in them will
bring you to the limit of table file size much more quickly. If your
average image is 1 MB and your file system's maximum file size is 2 GB
(there are still some older Linux servers whose file systems have this
constraint), you will only be able to store about 2,000 images until
your table exceeds the maximum allowed file size.

Few Linux implementations have a 2GB limit any more. Most are at least
4GB, and some implementations are much larger.

Yes, but it's an issue the OP needs to check on before commencing
development.

Also, the typical image size is *much smaller* than 1MB.

Depends on where you are and what you do. The OP mentioned an "image
competition", which could be en event where average image size is much
LARGER than 1 MB. A digital photo shot by a professional (or even top-
of-the line amateur) camera can easily be 10 MB, so can a piece of
digital artwork or a magazine page layout.

Our company is starting an image-competition soon, and I am not sure
if I should write the php script to insert the binary code into a
mySQL database or if I should just store the files in a dedicated
folder and the data about them in the mySQL database.

Large image sharing sites invariably stick to the second approach.
Moreover, images may be stored on separate servers, optimized for
serving static content.

An over-generalization. Many have images stored in the database itself.

Please name one. I, meanwhile, will follow my own advice and do the
same. In his book, "Building Scalable Web Sites", Cal Henderson,
chief software architect of Flickr, describes Flickr's image storage
facility (p.152): disk-based and redundant, with synchronous writes to
primary storage and asynchronous writes to multiple backup storage
locations.

Cheers,
NC
.

Relevant Pages

  • Re: ADO Connection Timeout
    ... to the central server, but you are willing to live with periods where it ... i.e. a local database or even a text file. ... to function until the connection can be restored to the server. ...
    (microsoft.public.data.ado)
  • Complicated Connection Problems bewteen ADP and SQL Server
    ... This database ... expertise for getting the user workstations talking to the SQL Server. ... connection would fail and the adp wouldn't be able to talk to the server. ... might be in my ADO connection string. ...
    (microsoft.public.access.adp.sqlserver)
  • Complicated Connection Problem between ADP and SQL Server
    ... This database ... expertise for getting the user workstations talking to the SQL Server. ... connection would fail and the adp wouldn't be able to talk to the server. ... might be in my ADO connection string. ...
    (microsoft.public.sqlserver.connect)
  • Re: ADO Connection Timeout
    ... so what happens when a connection failure forces one station to revert ... to a local database? ... Further, you *will* have contention issues, Jet does not support record ... to the central server, but you are willing to live with periods where it ...
    (microsoft.public.data.ado)
  • Re: ADO Connection Timeout
    ... When the first test is run, the results are stored in the central database. ... to the central server, but you are willing to live with periods where it ... i.e. a local database or even a text file. ... to function until the connection can be restored to the server. ...
    (microsoft.public.data.ado)