Hello to all of you beautifull people out there, I am new to MySQL, i just wanted to know if its a good practice to directly store images and other binary files in a MySQL database. Any one with help? Thanks

Reply

Well, in these days where we can get incredible storage devices -hard disks, dvds, cds, etc.- with huge capacities, i dont know exactly if it is a good practice to do that because i dont have any statistics about it, i think that it is not a problem to store binary data directly in a database.

Personally i don't do that, in all cases that i know that i gonna deal with this situation i always prefer to store in the database only a reference to the image or binary data using a string fields to alocate its file name and folder name.

Best regards,

Reply

It is a practice used by many developers I know. It is possible to store a binary image file such as a .jpeg or .gif. However, I recall working on a project where doing this was the "easy way out." However, I recall this solution tends not the be the most efficient way of doing things.

When my colleague and I tried this, we noticed that the SQL databases became very large. Furthermore query processing tend to be slower. Things just felt "sluggish." We did not think anything was wrong, as we've read that it was a legitimate practice to store images directly, in binary, in the SQL database. However, further research revealed that there is a more efficient way.

We dumped all the images in a read-only directory. We simply converted the "IMAGE" column field to "IMAGE_PATH." We simply stored the paths of the images in our "image dump" directory under this field.

So, we simply pointed the SQL database in the right direction, when an image query is made.

The database behaved much better after these modifications. Everything just felt more efficient.

Hope this helps, good luck.
- Demirelli

 

 

 

Reply

Yeah, in my practice I found that it is much better to store your images as binary files somewhere and not in a database like mysql, because it is much faster for apache as a httpd server to just transfer a binary file to a user with the right headers, same with a html file or any other file which doesn't need parsing, for example php. Using extra resources, like mysql database, which requires different sql querys, imagine you need to show to a user 10 or 20 images at one request.. imagine you have 100 MB of pictures/images..

Besides, it is much easier to organize your pictures when they are stored as binary files in some directories, you just upload them and similar, you can give an id for every image if you want to or if you don't want to show the image directory, you can worj with the image using a language like PHP.. you won't need to waste time to store the images into databases, you won't need to waste time to see what you have in a database if talking about images or other binary media. Imagine you have a database with 20GB of images, you would have a lot of problems, due to making backups or restoring backups would be hard, repairing tables would be hard to or would take time and etc.

The only good way of storing binary data to a mysql database for me is that if you have a very big site or something, which requires a lot of resources and you can spend money for a mysql server just for binary data, when it is a very good way, because all the other servers which are running can get the image by making a mysql query and if you have 20 servers, you don't really need to have the images stored (imagine if images take over 36GB) on every machine, you save space that way, but for simple developers and websites, I don't see anything good about that.

Reply

Have a look at the way, here offered by astahost cpanel, the 4image system works.
In the mysql database are stored the data and the indexes describing the images and the path to have the images and which thumbnails are associated with, and where the thumbnails are, and the guest's comments. Each set of database data points to the place where the htmls images are stored, these are subfolderes named 1, 2, 3, etc.. in the "media" folder, so everything is inside the Unix or windows fileystem.
so, the php programs offer the logic, the mysql database offers the data organizing, and the data are in filesystems. This is the ideal combination of the stongpoint of each component.

Reply