Comparison between storing images/files in mySQL and on filesystem.

There was a discussion in regard to “store images in MySQL” in one of WebHostingTalk forum. Many believe that storing images/large set of data in mySQL will hurt performance, specially when you want to do query. Unfortunately I could not find any resource in regard to this or support this. So I set off to do my own performance test run. The test is written using PHP, since PHP/mySQL is such a popular combination.

Test run are ran on following specs:

CPU: Intel 4 3.06GHz
RAM: 1 GB.
Linux Distribution: Ubuntu 6.2 Server edition

mySQL version 5.0.45
PHP-cli 5.2.3

The image file is 2.4MB, in total there is 1000 copies in the database. Overall, the physical size of the database file is 2.5GB.

The database structure contain 2 tables, image and imageblob.

• Table image have id, name, imageid and imagelocation as it’s field.
• Table imageblob have id and image as it’s field.

Let me explain what each field contain;

• image.id – primary id, integer type, unique and incremental.
• image.name – varchar type.
• image.imageid – forgein key to imageblob table, map to imageblob.id, indexed.
• image.imagelocation – text type, to store path of the image location.
• imageblob.id – primary id, integer type.
• imageblob.image – mediumblob type, this is where the binary data is stored.

Why did I have 2 table? I will let tell you later.

So first thing I do is insert the image file into those 2 tables.

mysql_connect("localhost", "test", "xxxxxx");
mysql_select_db("mysqltester_blob");
$filecontent = file_get_contents("map_large_community.png", FILE_BINARY);
$imgid = 0;

for ($i = 1; $i < 1000; $i++) {
$imgid = $i;
mysql_query("INSERT INTO imageblob (id, image) VALUES (" . $imgid . ",'".mysql_real_escape_string($filecontent)."')");
mysql_query("INSERT INTO image (name, imageid) VALUES ('testing".$i."', " .$imgid. ")");
}

I ran the above script 3 time to profile the performance. 1000 records were insert into the table in 2minutes and 38 secs. That translate to 0.158sec per record. Not bad, for each record containing 2.4MB worth of data.

Next step is to do the selection from those table. I will need 2 PHP script,

1. Will query the table and retrieve the image from the file system.
2. Will query the table and retrieve the image from the database.

————————-

mysql_connect("localhost", "test", "xxxxxx");
mysql_select_db("mysqltester_blob");

for ($i = 0; $i < 10; $i++) {
$result = mysql_query("SELECT image.id, name, imagelocation FROM image WHERE name = 'testing".rand(1,500)."'");
$output = file_get_contents("map_large_team.png", FILE_BINARY);

while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
printf("ID: %s  Name: %s Size: %d\n", $row[0], $row[1], strlen($output));
}
}

This php script will retrieve image from the file-system after database query. It ran 3 times and the results are:

• 0.144sec
• 0.144sec
• 0.144sec

————————-

mysql_connect("localhost", "tester", "xxxxxx");
mysql_select_db("mysqltester_blob");

for ($i = 0; $i < 10; $i++) {
$result = mysql_query("SELECT image.id, name, image FROM image inner join imageblob on image.imageid = imageblob.id WHERE image.name = 'testing".rand(1,1000)."'");

while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
printf("ID: %s  Name: %s Size: %d\n", $row[0], $row[1], strlen($row[2]));
}
}

This php script will retrieve image from the database. It ran 3 times and the results are:

• 1.389sec
• 1.337sec
• 1.256sec

————————-

As you can see from the result, the difference of performance very minimal. At the beginning of the test run, I was using 1 table to store both image detail and image blog. The performance was extremely poor, a similar SELECT query on name took a few minute to complete. I think it is because mySQL is going through all the huge blob column while search for the matching name. Splitting image detail and the image content help the query speed up tremendously. That is why I have split the data into 2 tables. Another important tip is image.imageid and imageblob.id must be indexed, it also help with the query.

In conclusion, we as programmer should see store large document/image in database as a solution. It’s performance is comparable to storing large document/image on file system. There are many advantage for store large data on database. At the end, as long as the table is structure correctly, the performance issue will not be an issue.