Storing files in the database

From #mysql Freenode

Short Answer

Yes.

Long Answer

Yes, but there are several good reasons not to do so.

• The file system will handle/cache the files better.
• If it's a web application, the files will be cached in proxy servers outside your system thereby reducing the load on your server.
• The MySQL server will be able to cache normal table data far more easily.
• The MySQL server's data throughput will be higher.
• It will be easier to reorganise the data and jpg/mp3's to different drives/filesystems.

In other words, simply storing pointers to your jpg/mp3's as they exist in the filesystem is often a better idea than storing binary files in the database.

That said, there are some things that you gain by putting it in the database.

• You will have one centralized place for backups.
• You can gain ACID-consistency between images and references to them (This will be hard to get if you store them in the filesystem).
• It is easy to distribute if over replication to multiple servers.

Best of Both Worlds

Jim Starkey, author of the Falcon transaction engine and inventor of the BLOB data type, says the database is a great place to store images, for the reasons stated above. However, requesting the image from the db on every page request is silly. Instead, use a sort of cache, where the image when first requested is written to a file, and served from that file. When the value changes in the database, flush that file. Best of both worlds.