What is the best way to store media files in a database?

Question

I want to store a large number of sound files in a database, but I do not know if this is good practice. I would like to know the pros and cons of doing it this way.

I also thought about the possibility of having “links” to these files, but perhaps this will have more problems than solutions. Any experience in this direction would be welcome :)

Note. The database will be MySQL.

Answer 1

Each system that I know stores a large number of large files, stores them externally in a database. You save all the requested data for the file (name, artist, length, etc.) in the database, as well as a partial path to the file. When it's time to get the file, you extract the file path, add some file root (or URL) to it, and return it.

So, you will have a “location” column with a partial path in it, for example “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 specify the media database in another server / directory, if necessary for data recovery. In addition, a procedure may be required that re-synchronizes the database with the contents of the file archive.

In addition, if you are going to have thousands of media files, do not store them in one giant directory - this is a performance bottleneck in some file systems. Instead, split them into several balanced subtrees.

Answer 2

I think that storing them in the database is fine if you use a good implementation. You can read this older but good article for ideas on how to save more data in the database from performance impact.

http://www.dreamwerx.net/phpforum/?id=1

I had literally 100 concerts loaded into mysql databases without any problems. Design and implementation are key, do it wrong, and you will suffer.

Additional advantages of the database (not yet mentioned): - Works better in a balanced workload environment - You can create a more scalable storage system

Answer 3

I experimented in different projects, doing this in both directions, and we finally decided that it was easier to use a file system. In the end, the file system is already optimized for storing, retrieving and indexing files.

The only advice I would like to get is to save only the "root relative" path to the file in the database, and then your program or your requests / stored procedures / intermediate product use the root-specific installation option to get the file.

For example, if you store XYZ.Wav in C: \ MyProgram \ Data \ Sounds \ X \, the full path will be

C:\MyProgram\Data\Sounds\X\XYZ.Wav 

But you would save the path and / or file name in the database as:

 X\XYZ.Wav 

Elsewhere, in the database or in the program configuration files, save the root path, for example SoundFilePath, equal to

C: \ MyProgram \ Data \ Sounds \

Of course, if you shared the root of the database path to you. Thus, if you move your installation of the program, you do not need to update the database.

Also, if there will be many files, find a way to hash the paths so that you do not finish working with a single directory containing hundreds or thousands of files (in my small example there are subdirectories based on the first character of the file name, but you can go deeper or use random hashes). It also makes search indexes happy.

Answer 4

Benefits of using a database:

• Easy connection of audio files with other data bits.
• Avoid file I / O operations that bypass database security.
• No need to perform split operations to delete audio files when database records are deleted.

Disadvantages of using a database:

• Database growth
• Databases can be more expensive than file systems.

Answer 5

You can store them as BLOB (or LONGBLOB) and then retrieve data when you want to access media files.

or

You can simply save media files to disk and save metadata to the database.

I am inclined to the last method. I do not know how this is done in general in the world, but I suspect that many others will do the same.

You can store links (partial data paths) and then retrieve this information. It's easy to move things on disks and still access them.

I save the relative path of each file in the database along with other file metadata. The base path can be changed on the fly if I need to move the actual data to another drive (local or through the UNC path).

How am i doing this. I'm sure others will have ideas too.

Answer 6

Some advantages of using blobs for file storage

• Lower management overhead - use one backup / restore tool, etc.
• The inability to synchronize the database and file system.
• Transactional capabilities (if necessary)

Some disadvantages

• explodes the RAM of your database with useless garbage that it can use to store strings, indexes, etc.
• Makes your database backups very large, therefore less manageable
• Not as convenient as a file system for servicing clients (for example, with a web server).

---

What about performance? Your mileage may vary. File systems are extremely diverse, so databases are in their performance. In some cases, the file system will win (possibly with fewer files). In some cases, the database may be better (perhaps with a very large number of small files).

In any case, do not worry, do what seems best at the time.

Some databases have a built-in web server for serving blobs. At the time of writing, MySQL is not working.

Answer 7

Store them as external files. Then save the path in the varchar field. Placing large binary blocks in a relational database is usually very inefficient - they use only space and slow down because full caches are unusable. And nothing will work out - blobs themselves will not be able to search. You might want to save the metadata in the metadata to the database.

Answer 8

A simple solution would be to simply save the relative file locations as strings and let the file system handle it. I tried it in a project (we stored attachment files in an office file), and it worked fine.

Answer 9

The best way to store audio / video files, you can use any distributed storage that can be local or cloud.

https://min.io/

for the cloud: AWS S3