Images, PDF, audio and video files in MySQL

Question

I am designing a database for an eCommerce website.

How can I store images, audio, video and PDF files which are uploaded from the online users, in a MySQL database?

I am using the InnoDB storage engine. While surfing on the Internet, I got the following answers:

1. I can store these files using BLOBs.
2. I can use the Varchar datatype to store the physical path to store these files.

According to my previous experience storing the files on the database has some issues while retrieving that files (sometimes I got broken files).

What is the best practice to store these files in my MySQL database? As I am beginner, I need to know the performance implications for my database in such situations.

Answer 1

My recommendation is, as Max has suggested, to store on the filesystem and store a path in the db as varchar. This has the advantage of not having to deal with the files in the db. Even on other db's I think storing frequently used binary files in the db is asking for performance issues (storing less frequently used files in the db, OTOH, is a good thing).

However it does have the disadvantage of requiring multiple backups to get all the data your data needs. A secondary option might be to store the files in the db as a blog (in a separate table) and then store it on the filesystem too. This way you can just recreate the file from the db if it is missing and you can count on your database to be a complete set of the files for backup purposes. This may be needless complexity though.

Also if you are storing video files in the db, one real reason to go to PostgreSQL would be the stream-based LOB interface which would allow you to chunk the output.