Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
You probably don't want to store them in the actual database. You may want simply save the files onto a file system accessible to your web app, and store the location in the database. –  Max Vernon Mar 13 '13 at 13:50
add comment

1 Answer

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.