Retrieval from Database v Flat Files

[AndrewD]

Can anyone compare the efficiency of storing and pulling large files (0.5-5 Mbytes) out of an SQL database compared to straightforward hyperlinks to flat files?

A large part of our board is used to give our members access to reports, presentations, etc. These are currently stored as individual files (pdf, etc) within the web site and accessed via [URL] tags in the posts. I avoided using the VBulletin facility to handle these as attachments, primarily because there can only be one attachment per post.

As we built the site up, we really need a proper document management system to index, search, etc, this material. I'm thinking about the best way to do this. Storing everything in the database would produce a database that is half a gigabyte and growing, but obviously helps us handle security, access, etc. Flat files keep the database small but need discipline with file names, security, etc. Any comments/advice?

thanks

[filburt1]

If it's just a direct link to an actual file then that wins, athough IIRC MySQL can compress data which means that the data will take up less space in the end.

Of course it'll be harder to implement permission control with directly linked flat files...

[Xenon]

it depends also on your setup.
do you have your fileserver devided from your DB server?
compare the loads of both servers to see which one will handle it better