Storing text and audio files in MySQL

Question

Is it a good practice to store just the file path in the database? What are the advantages of storing the files itself in MySQL? If you store the files directly to MySQL, what are the data types for text files and audio files?

Answer 1

I suggest storing the audio and video files in some storage device and only store their paths and meta information in the database. When you need to retrieve these media files, then look for their paths in the database.

Every system I know of that stores large numbers of big files stores them externally to the database. You store all of the queryable data for the file (title, artist, length, etc) in the database, along with a partial path to the file. When it's time to retrieve the file, you extract the file's path, prepend some file root (or URL) to it, and return that.

So, you'd have a "location" column, with a partial path in it, like "a/b/c/1000", which you then map to: "http://myserver/files/a/b/c/1000.mp3"

Make sure that you have an easy way to point the media database at a different server/directory, in case you need that for data recovery. Also, you might need a routine that re-syncs the database with the contents of the file archive.

Also, if you're going to have thousands of media files, don't store them all in one giant directory - that's a performance bottleneck on some file systems. Instead,break them up into multiple balanced sub-trees.

Answer 2

MEDIUMBLOB - For Audio File

MEDIUMBLOB gives you up to 16 MB of data -- most likely plenty of room for any MP3 you'd have, and the size hit you take is length+3.

From the Documentation

For TextFile

     Type | Maximum length
-----------+-------------------------------------
  TINYTEXT |           255 (2 8−1) bytes
      TEXT |        65,535 (216−1) bytes = 64 KiB
MEDIUMTEXT |    16,777,215 (224−1) bytes = 16 MiB
  LONGTEXT | 4,294,967,295 (232−1) bytes =  4 GiB

Note that the number of characters that can be stored in your column will depend on the character encoding.

Answer 3

When you store files in SQL you are actually storing their Byte data using either BINARY or VARBINARY data types, if you were to store files in SQL you will have to encode and decode the files as needed and you would be potentially duplicating data. However, the problem with storing the files as files is that you lack flexibility to move them around. Whats "best" depends on the use case.