storing images in mysql or in filesystem better?

[anita999]

I am creating a site that will store thousands of images using a form input/PHP.  Is it better to store the image in a filesystem and have a link to it from the mysql DB?  Or is it better to store it directly in the mysql DB as BLOB?  The images would be displayed on the Web.  I need strong reasons for filesystem versus mysql DB.  Here are some items and thoughts:

 

1. pictures in my site would be stable i.e. they would be relatively the same over a period of months.

 

2. If we don't use DB, we will have to manage the pictures ourselves on filesystem, i.e.

  sorting, unique ID, backup, portability, mobility from one server

  to another, etc...

 

3. I would agree that there is cost to insert and query pictures from DB,

  the question is how costly it is compared to # 2

 

This problem must exist for sites that manage photos or video clip.

I believe that sites like YouTube do use DB instead of plain file systems

for the reason as described in item #2.  Does anyone know?

 

I can see the point of using some kind of pointers stored in DB, only the pointers but still need to think how about the back end and operation of file systems vs DB.

 

Any advice is much appreciated.

 

Thanks,

Anita

[veridicus]

I always store images in the file system.  Along with the pros and cons you mention, there's the lowering of overhead by letting apache directly return images from the file system.  My IMG tags typically point directly to the image files on the system, skipping PHP and mysql processing.  The images are always named with a consistent naming convention, usually a string describing the type to relate to a table and the ID (e.g. product-123.jpg), so I don't even need a field in the DB to know where the image is.  The PHP simply generates the paths.

 

I find this to be best for performance.  For large sites I use algorithms to store images in various directories.  For the largest site I've worked on with millions of pages, storing images in the database would have brought the replicated database servers to their knees.