which is heavier to load? the Blob Image from MySQL or directory linking?

Question

i am a newbie PHP programmer, i know how to store files in MySQL using Blob and just stroring it in folders and just link it with

or via blob


now which of the two aproach loads faster?

or what approach that deals with downloading images in the page is faster?

Answer 1

In my opinion, storing images in the file system is the preferred approach.

Let's say you want to display five images on a page. So your page will query the database to find five images that should appear on the page and pass their identifying information to five instances of a picture-rendering script. If you store pictures in a database, this will mean that each user viewing the page will trigger six simultaneous connections to the database. If you store images on disk instead, you can avoid the need for the picture-rendering script to query the database and reduce the number of connections to one.

Answer 2

I will assume by directory linking you mean to just save a reference to a file location in a column and then retrieve that file (BLOB) by looking up the path from the databsae and getting the file data from the file system.

I will assume that you dont need to search on the BLOB data or manipulate it in the database. I work with document management systems and this was one of our design decisions. The concerns I address here also only to large databases, like I say... we work with document management systems where people often have tens (sometimes hundreds) of thousands of documents.

I would recommend saving only a path in the database. From a maintenance point of view, your database easily grows large and in backups become cumbersome. It will also slow down replication tremendously. By saving a path in the database, you simplify this.

If you expect a lot of database traffic on the BLOB data, this will create a bottleneck in the database. By saving path outside database, your application can scale better because in a high demand environment, you can have a file server and a database server. With the BLOB, you cant do this.

Answer 3

In my shortetest time as a PHP/MySQL programmer, I have found out that its very easy for me to store the image names in the tables which not only keeps my tables small and the data held within them also are small. What I do next is just link in my script like this:



the sample table will be someting like this

create table photos(
photoID int(6) not null auto_increment,
photoCaption varchar(200) not null,
photoImageFile varchar(120),
primary key(photoID));