Database file storage

[zAlbee]

I'm setting up a database that can store user-uploaded files amongst other things. It's a PHP MySQL server. So I go to all this trouble to set everything up, with a BLOB field for the file, and varchars/ints for mime-type, filename, filesize, etc. Now I'm reading that some people think storing the file directly into the database is terrible for performance, and it would be better to store the file in a directory and just store the path in the database.



Maybe it is true, but I have never read about this "performance hit" in official documentation.

Any thoughts?

[ScottJC]

Storing a file in a database is harder than pointing to its path, but it adds security, i.e. file.php?db=filename.type, because the path of the true file is completely embeded in the database and nobody knows exactly where the database is then leeching of other sites is considerably harder.

However: The database would increase in size exponentially as it is used, so it may be a better idea to store it as seperate files. The larger the database the slower it becomes and your php/cgi whatever will have to be involved throughout the entire progress of the user downloading.

You can get around that though by using more than one database and perhaps creating new ones on the fly.

[Falcon4ever]

Storing files as a blob in a MYSQL db is not recommended ,
it's better to make a filehash, renamed the uploaded file and store the properties of those into the mysql db.

[Cyberman]

That's what this site does.

The alternative is to create 2 seperate databases 1 with the BLOB files and 1 with the Reference too that file. This at the very least prevents security issues. The big problem comes when you perform maintenance on the database. And don't EVER say that it won't be necessary. Deleting a 1 meg section from a database that's about 1421 megs in size, is a nightmare.

Have fun, I suggest experimenting before you end up comitted to your design

Cyb

We are slaves of our own designs.

[zAlbee]

Thanks guys. From what I've read, using the filesystem is much faster, and is much better for the database. I like your suggestion Falcon4ever, I think I'll do something similar .

As for file retrieval... is there any combination of HTTP headers that will both redirect to a file and rename it (ie. set the name for downloading)? Otherwise I think I'm stuck with doing a PHP readfile() =P