Is better store images in a BLOB or in directories?

[XIMLabz]

Hi all!
This is the first time I write in these forums: so far I've searched the solutions to problems that I had between existing discussions.
I tried to find a good answer to my question, but there're many point of view and many ways of thinking about this solution. :(
The question is:
Is better put images in database like MySql with BLOB type or store images in usual directory, linked by filename stored in database?
My situation is this:
I'm about to start writing a web interface that allows to upload and manage images that will be voted and then reordered for other votations. The images will not exceed 3 MB and they'll be supported only JPG, PNG and GIF extensions.
The number of the images will be around 500 per year. Naturally we'll use this images voting method for all the years to come.
There's nobody that tell me what is the better way? Store in DB or in directories?
Thanks in advance!

[cereal]

Hi,

in my opinion, it is better to save the images in directories and the names in a database, mainly because you can serve the images as static content, without involving the PHP engine and the database in this output process.

[JorgeM]

but there're many point of view and many ways of thinking about this solution.

That's true because there is no right or wrong way regarding this topic.

I also prefer storing images and uploaded files on the file system.

However, if you read comments about those that store this in the DB instead, there are reasonsable reasons why to store in the DB instead. Such as managing the files can be easier if its all stored in one central DB. Files on the file system can be deleted or added without outside of the application scope/process, if you store in the DB you dont have to worry about that. Migrating is also easier because you dont have to worry about thousands, tens of thousands of files to move, because its all in the db.

If you are hosting online, many providers limit you the amount of space in your DB. That is one reason why many go with storing files on the file system since you are given much more room there. Plus, if you store files away from the DB, you can easily use additional hardware to serve up those static files (as mentioned by cereal) and spread the load.

[XIMLabz]

Thanks for the replies.
But, another answer:
If I haven't got limits problem (and I don't have the migrating needs), the fastest solution is storing images data in DB?

[cereal]

You can do a test: load 500 files in a table database, then create a duplicate of this table without the blob field and perform some queries. MySQL provides a tool, the name is mysqlslap, that can give you some information about the performance of the queries:

• http://dev.mysql.com/doc/refman/5.6/en/mysqlslap.html

I created a simple script that reads a directory and saves the images to a table imagedb, if you want to try, below there is the code. Start from the table:

CREATE TABLE `imagedb` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `file` mediumblob NOT NULL,
  `filename` char(32) NOT NULL,
  `extension` varchar(5) NOT NULL,
  `size` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `filename` (`filename`),
  KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

The file column will save the binary data. This is the script:

<?php

# database config
$dbconfig = array(
    'hostname' => 'localhost',
    'database' => 'dbname',
    'username' => 'dbuser',
    'password' => 'dbpass'
);

# config
$filespath = '/path/to/images'; // SET THIS
$exts = array('jpg', 'gif', 'png'); // allowed extensions
$listlimit = 500; // stop at 500 inserts
$sizelimit = 3500000; // soft limit at 3.5MB

if( ! is_dir($filespath)) exit('Directory does not exists!');

try
{
    $pdo = new PDO("mysql:host=$dbconfig[hostname];dbname=$dbconfig[database]", $dbconfig['username'], $dbconfig['password']);
    $pdo->setAttribute(PDO::ATTR_PERSISTENT, TRUE);

    $stmt = $pdo->prepare("INSERT IGNORE INTO imagedb (file, filename, extension, size, created_at, updated_at) values(:file, :filename, :ext, :size, :dt, :dt)");

    # internal counters
    $i = 0;
    $e = 0;

    $dir = dir(rtrim($filespath, '/') . '/');
    while(FALSE !== ($f = $dir->read()))
    {
        $file = pathinfo($f);

        if(in_array(mb_strtolower($file['extension']), $exts))
        {
            $fsize = filesize($dir->path . $f);
            $e++;

            # file is too big, skip
            if($fsize >= $sizelimit || $fsize === FALSE)
            {
                $e--;
                echo PHP_EOL;
                echo "\033[1m{$e}\t{$f} is too big {$fsize} bytes\033[0m" . PHP_EOL;
                echo PHP_EOL;
                continue;
            }

            $fdata = file_get_contents($dir->path . $f);
            $dt = date('Y-m-d G:i:s');

            $stmt->bindParam(':file', $fdata, PDO::PARAM_LOB);
            $stmt->bindParam(':filename', md5($file['filename']), PDO::PARAM_STR, 32);
            $stmt->bindParam(':ext', $file['extension'], PDO::PARAM_STR, 3);
            $stmt->bindParam(':size', $fsize, PDO::PARAM_INT);
            $stmt->bindParam(':dt', $dt, PDO::PARAM_STR);
            $stmt->execute();

            echo "{$i}\tfile: ". md5($file['filename']).'.'.$file['extension'] ."\t was: {$f}". PHP_EOL;

            $i++;
        }

        if($i >= $listlimit) break;
    }

    # close connection
    $pdo = null;

    # close Directory reader
    $dir->close();

}
catch(PDOException $e)
{
    echo "\033[1m" . $e->getMessage() ."\033[0m". PHP_EOL;
    die();
}

The script will print a list of the included files, while the action is performed and it will outline those that are too big. Run it in a linux terminal:

php imagedb.php

Now, if you open a shell with the database you can see the data is loaded:

select count(id) from imagedb;

+-----------+
| count(id) |
+-----------+
|       500 |
+-----------+
1 row in set (0.00 sec)

At this point you need to create a table like the previous, but without the file column, so you can perform these actions:

create table imagetxt like imagedb;

-- drop the file column
alter table imagetxt drop file;

-- copy the data to the new table
insert into imagetxt (filename, extension, size, created_at, updated_at) select filename, extension, size, created_at, updated_at from imagedb;

Now you have the same identical data in both tables, except that the former has the blob column. If everything went fine, then try to perform mysqlslap from your terminal, let's start with the imagetxt table:

mysqlslap -uUSER -pPASS -h localhost --create-schema="dbname" --query="SELECT * FROM imagetxt WHERE size BETWEEN 10000 AND 2000000;" --concurrency=10 --iterations=20

In my case outputs:

Benchmark
Average number of seconds to run all queries: 0.028 seconds
Minimum number of seconds to run all queries: 0.003 seconds
Maximum number of seconds to run all queries: 0.485 seconds
Number of clients running queries: 10
Average number of queries per client: 1

And then slap the imagedb table too:

mysqlslap -uUSER -pPASS -h localhost --create-schema="dbname" --query="SELECT * FROM imagedb WHERE size BETWEEN 10000 AND 2000000;" --concurrency=10 --iterations=20

Which in my case outputs:

Benchmark
Average number of seconds to run all queries: 5.541 seconds
Minimum number of seconds to run all queries: 4.928 seconds
Maximum number of seconds to run all queries: 13.095 seconds
Number of clients running queries: 10
Average number of queries per client: 1

Which is slower. BUT it happens because I'm selecting the blob column too, I'm trying to retrieve almost all 500 rows and I have not tuned the database to use more RAM to store the results in the query cache.

So, in order to decide you can setup some example tables and test the queries you want to use in your website. The benchmark may not be a perfect tool, but it can give you an idea of the performance of your database schema under stress conditions. Hope it helps, bye!