Storing and Retrieving Large Image Files in MySQL: Comparing LONGBLOB, LONGTEXT, and VARCHAR

Background: MySQL provides TEXT and BLOB types for storing large objects such as images and videos, but storing many gigabytes directly in the database can cause performance and space issues.

Example: a 5 MB image and a 2 GB video illustrate the problem; storing 1,000 videos would occupy 2 TB, which stresses disk usage more than row count.

Instead of storing the binary data, it is usually better to store only the file path in the database and keep the files on the filesystem.

Three tables are created to compare storage methods:

CREATE TABLE `tt_image1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `image_file` longblob, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `tt_image2` ( `id` int(11) NOT NULL AUTO_INCREMENT, `image_file` longtext, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `tt_image3` ( `id` int(11) NOT NULL AUTO_INCREMENT, `image_file` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

100 copies of a 5 MB image are generated and inserted into each table using shell loops and MySQL commands (load_file, hex(load_file), and direct path insertion).

After insertion, the record counts are verified and the on‑disk sizes of the .ibd files are compared, showing LONGTEXT uses the most space, LONGBLOB about half, and the VARCHAR path column the least.

Retrieving data:

For the path table (tt_image3) a simple SELECT returns the file path.

For the LONGBLOB table (tt_image1) a stored procedure is provided to dump each binary image to a file using a loop with PREPARE/EXECUTE and the DUMPFILE clause.

DELIMITER $$
USE `ytt`$$
DROP PROCEDURE IF EXISTS `sp_get_image`$$
CREATE DEFINER=`ytt`@`localhost` PROCEDURE `sp_get_image`()
BEGIN
  DECLARE i,cnt INT DEFAULT 0;
  SELECT COUNT(*) FROM tt_image1 INTO cnt;
  WHILE i < cnt DO
    SET @stmt = CONCAT('SELECT image_file FROM tt_image1 LIMIT ',i,',1 INTO DUMPFILE ''/var/lib/mysql-files/image',i,'.jpg''');
    PREPARE s1 FROM @stmt;
    EXECUTE s1;
    DROP PREPARE s1;
    SET i = i + 1;
  END WHILE;
END$$
DELIMITER ;

For the LONGTEXT table (tt_image2) the same approach works by converting the hex string back to binary with UNHEX().

Conclusion: Storing large binary files directly in MySQL consumes significant disk space and complicates backup and I/O; storing only file paths is more space‑efficient and easier to manage.