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.

SQL queries can be very tasking and use a lot of ram. Static files will always be faster because they are cacheable whereas SQL you need a reverse proxy to cache it. However, with good hardware and optimized software, I believe they can be equally as fast but you will need better hardware for SQL, that's a fact should it be SQL text or blob. The SQL would be larger on disk because it stores the image and then the table/database data, but this is minimal and only a few bytes. Also see: stackoverflow.com/questions/3748/… — Simon Hayter
– Simon Hayter, Commented May 6, 2017 at 10:18
Thanks Simon. I had actually built the site with static files, but then got the strange feeling that storing as a blob might be better. I'll stick with static files for this and my future sites. Thanks again. — Wayne Six
– Wayne Six, Commented May 6, 2017 at 10:51
We get these questions a lot. For time sensitive delivery, nothing beats relying on the web server as the most efficient delivery mechanism. It is okay to use blobs, however, it is tasking and not generally for sites that are hosted. If you owned a beast of a SQL based database sever, a well planned schema can deliver images, but still not recommended. Blobs are compressed. Compression and decompression is the primary concern. It is possible to turn off compression or change the compression to be more efficient. Also, cache can be tweaked to really speed things up. Not worth it. — closetnoc
– closetnoc, Commented May 7, 2017 at 4:44

Mike -- No longer here · Accepted Answer · 2017-05-06 20:40:23Z

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.

There are times to use blobs (Android changed to boobs) in a data base. Generally, using blobs to store images for time sensitive processing such as the web is not a good idea. On faster SQL based severs, this can be avoided except for scale. Storing a lot of images would be dependent upon the schema. As well, and I forget the exact mechanism, instead of blob you can use a pointer to a file, not the way you did, but a real pointer where the data is stored externally so that some limits are removed. Still, for hosted sites, keeping images out of the database is best. Cheers!! — closetnoc
– closetnoc, Commented May 7, 2017 at 4:36
It occurred to me to tell you that your recommendation is exactly what I would be doing with a table and a URL I could simply plug into my HTML. I did not want my comment to indicate anything else. With MySQL, you can configure a column to be stored externally and the dbms will do it automatically for you. As well, what makes databases slower is the compression and decompression. This can be made faster and with database look ahead caches images can be stored in a database. Still without a beast of a machine, I recommend against it especially in a hosting environment. — closetnoc
– closetnoc, Commented May 7, 2017 at 5:55
Well, at least someone else besides me thinks about previous generation technology aka "...without a beast of a machine" — Mike -- No longer here
– Mike -- No longer here, Commented May 7, 2017 at 22:43
One sign of a true genius is one who finds the simplest solution first and uses that. Cheers!! — closetnoc
– closetnoc, Commented May 7, 2017 at 23:04

Stack Exchange Network

Using BLOB's for Small Magazine - PHP / MySQL

1 Answer 1

Your Answer

Hot Network Questions

Using BLOB's for Small Magazine - PHP / MySQL

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions