This is a question driven by curiosity. I am learning Oracle database and Oracle database handles Blob data in a separate tablespace, which I believe reduces overhead while trying to read large Blob data. I am wondering how MySQL handles Blob? I am actually planning to start a project and I wish to give a try on saving images in MySQL databases instead of standard approach of saving files in the file system.
Thank you.
1
If I interpret the following two links correctly at least when using InnoDB the blobs seems to be stored in the same B+Tree as the other data but depending on the row format the data can be stored in so-called overflow pages. In the (old) COMPACT format InnoDB stores the first 768 bytes of the page on page and the rest off page. The DYNAMIC format seems to store only the full value (if small enough) or a single pointer on the page. So this one seems better.
I personally have customers having roughly 20-30 GB of binary data in a MySQL database. The blobs are stored in a separate table and then just referenced via a key. I actually like this scheme better than filesystem storage since it works well for the small amount of data and if the data grows you can switch out the table to a different mechanism. The filesystem will also make it more difficult to add more nodes to your system since you will have to add a clustered/networked filesystem. In that way, you can use MySQL as a poor mans fileserver. It works quite well for small to medium workloads.
I am no MySQL or Oracle expert but I hope this helps.
Links
- blob-storage-in-innodb
- innodb-row-format.html