php files upload to DB vs directory

[mutago]

Assuming someone is developing a site similiar to facebook in content where users are allowed to upload of heavy files, images, videos, pdf etc.

what could be the php best option of coding and fast in workability

1: Is it the best to upload and retrieve the files to and from directories

                           OR

2: Is it the best to upload and retrieve the files to and from the database (what about the database performance) and what are the reasons for choosing any of the 2 options

3: Assuming someone have a web hosting Hard disk space of 5mb and uploads a files of about 4mb to database. will the size of the hard disk be affected because of uploaded files in the database.unlike it will be affected when files is uploaded to a directory

4: Recovring heavy files from database OR from directory, which of them will be faster and scalable..

5: mysql database does it have a limited data it can contain or those it tends to infinity.

I am just paranoid and so i need codecall experts on these

[cereal]

I think, most of the times, it is better to save the files to directories or to an external CDN (Content Delivery Network) because it is static data and the request can be executed without involving the PHP engine and MySQL server.

If you're not using a CDN service, you can set up few subdomains to multiply the downloads of the files: when you open a page the browser downloads only two images for each domain, so if you set four subdomains as:

• img001.domain.tld
• img002.domain.tld
• img003.domain.tld
• img004.domain.tld

Your website delivers up to 8 images at the same time. This can be done without involving Apache, PHP and MySQL, but using a load balancer with thttpd or directly the nginx server. You gain in performance.

Regarding the space occupied, it depends: if the database is in the same partition of the webserver, then it will occupy the same space as you save the file into a directory, there is almost no difference, maybe just few bytes more into the database.

If the database is external, then obviously the space will be occupied only for the time needed to upload the file and save it to the database, then it will use the disk space of the database server. But in this case you have to check some limitations about the size of the query you can perform: if I'm not wrong the default is about 1024 bytes, and needs to be pushed up if you want to insert or select the files from the tables. Also you have to consider the bandwidth size and speed between the database and your server. If, for example, you have 100Mbit then you can move only 12.5MB/s

5: mysql database does it have a limited data it can contain or those it tends to infinity.

The limit is given by the max_allowed_packet that cannot be more than 1GB.

From my point of view, to serve static files use always directories, save the names in the tables and use the file-system as the best database for files that you can access, which by facts it is.

If you want to extend a directory system just add proxies, load balancers or CDN. To speed up a database system, among the other solutions, you will want to cache the queries, if you save images large 10MB, and want to return 1000 rows from the table, it will equal to 10GB of results, this cannot be cached efficently by the system memory, unless you don't have at least the double of that RAM. Also you will start to create caches of the files between the database and the application, and so you will end up to save them to the file system again. It doesn't pay to retrieve a file 10k times from the database, when you can get the static file with a simple read operation.

Check these links for more information:

• http://dev.mysql.com/doc/refman/5.5/en/packet-too-large.html
• http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_key_buffer_size
• http://developer.yahoo.com/performance/rules.html