Using BLOB's for Small Magazine - PHP / MySQL

Question

I'm building a small magazine website which I plan on using daily, but I'm wondering if I should store my article images and videos as physical files or as BLOB in my MySQL Table.

My primary concern is that I'm told it slows down your site if you use blob data, but I don't know if that's when using all information in a single table, or just using them period.

My ideal setup would be to have 2 tables:

article_table:

ID  | Title   | Image | Article
-------------------------------
int | varchar | int   | text



blob_table:

ID  | Name    | Blob
--------------------
int | varchar | blob

So, when I load up my article, based on the ID, I would then select my blob image where the ID's match:

if(isset($_GET["id"])){$id = $_GET["id"];}else{
header("Location: http://www.wesbite.com");
exit();
};

$blob_sql = "SELECT * FROM blob_table WHERE id = '$id'";
$blob_res = mysqli_query($con, $blob_sql);

Using this method, what would be the effect on having a vast mumber of records in the blob_table, when trying to display them on the article.php page?

Also, does anyone know if there is a difference in the size of a file that is physically stored vs that of a blob? i.e. if a physical JPG is 320KB, what would be the equivelant size of the blob table cell.

Finally - Is there a difference in the actual upload speeds when comparing uploading a blob to uploading a physical file?

Thank you.

Answer 1

I'm building a small magazine website which I plan on using daily, but I'm wondering if I should store my article images and videos as physical files or as BLOB in my MySQL Table.

Physical files. Less taxing on the server.

...I'm told it slows down your site if you use blob data, but I don't know if that's when using all information in a single table, or just using them period.

To store in a database, the database server has to be running in the background, then you're asking to store data in a blob which embeds your file into sql's internal database files in order to deal with the data, and when looking the file up, it has to go through the database files and extract the data for you.

My ideal setup would be to have 2 tables...Using this method, what would be the effect on having a vast mumber of records in the blob_table, when trying to display them on the article.php page?

The best way to do this is to create one table (not two) that provides information about the file as well as the location to the file. If the files need to be secure, then locate them outside the document root folder and have the script load and process them for the user.

So you could have a table like this:

ID | Description | filename
----------------------------
 1 | apples      | /home/myself/public_html/apple.jpg
 2 | oranges     | /home/myself/public_html/orange.jpg
 3 | bananas     | /home/secret/bananas.jpg

Also, does anyone know if there is a difference in the size of a file that is physically stored vs that of a blob? i.e. if a physical JPG is 320KB, what would be the equivelant size of the blob table cell.

It depends on SQL and version used. Each data type has different sizes and is dependant on how you declare the data. For example, if you declare an ID as BIGINT instead of TINYINT, then you're probably wasting 6 bytes per record. Storing the file as an actual file is both faster to process, and space saving, and doesn't require an SQL engine to process it.

Finally - Is there a difference in the actual upload speeds when comparing uploading a blob to uploading a physical file?

upload speeds depend primarily on how overloaded your server is. If millions of people access it at the same second, then chances are the upload may be drastically slow. When a guest uploads something, its for the most part undefined data because its the server script then after that determines what happens to the data. In PHP, you can make it where guests can upload files which is faster since you don't have to open the file on the server to convert it into a blob.