Does VARCHAR Length Really Impact MySQL Storage and Query Performance?

Problem Description

Database design guidelines often advise using the shortest possible variable‑length field that satisfies the requirements. The rationale is twofold: to save storage space and to improve query performance.

The author questions whether a VARCHAR(50) and a VARCHAR(500) truly differ in storage consumption and whether longer fields degrade query speed.

Validating Storage Difference

1. Create Two Tables

CREATE TABLE `category_info_varchar_50` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(50) NOT NULL COMMENT '分类名称',
  `is_show` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否展示：0 禁用，1启用',
  `sort` int(11) NOT NULL DEFAULT '0' COMMENT '序号',
  `deleted` tinyint(1) DEFAULT '0' COMMENT '是否删除',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime NOT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_name` (`name`) USING BTREE COMMENT '名称索引'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='分类';

CREATE TABLE `category_info_varchar_500` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(500) NOT NULL COMMENT '分类名称',
  `is_show` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否展示：0 禁用，1启用',
  `sort` int(11) NOT NULL DEFAULT '0' COMMENT '序号',
  `deleted` tinyint(1) DEFAULT '0' COMMENT '是否删除',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime NOT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_name` (`name`) USING BTREE COMMENT '名称索引'
) ENGINE=InnoDB AUTO_INCREMENT=288135 DEFAULT CHARSET=utf8mb4 COMMENT='分类';

2. Insert 1 Million Rows

DELIMITER $$
CREATE PROCEDURE batchInsertData(IN total INT)
BEGIN
    DECLARE start_idx INT DEFAULT 1;
    DECLARE end_idx INT;
    DECLARE batch_size INT DEFAULT 500;
    DECLARE insert_values TEXT;
    SET end_idx = LEAST(total, start_idx + batch_size - 1);
    WHILE start_idx <= total DO
        SET insert_values = '';
        WHILE start_idx <= end_idx DO
            SET insert_values = CONCAT(insert_values, CONCAT('(''name', start_idx, ''',0,0,0,NOW(),NOW()),'));
            SET start_idx = start_idx + 1;
        END WHILE;
        SET insert_values = LEFT(insert_values, LENGTH(insert_values) - 1);
        SET @sql = CONCAT('INSERT INTO category_info_varchar_50 (name, is_show, sort, deleted, create_time, update_time) VALUES ', insert_values, ';');
        PREPARE stmt FROM @sql; EXECUTE stmt;
        SET @sql = CONCAT('INSERT INTO category_info_varchar_500 (name, is_show, sort, deleted, create_time, update_time) VALUES ', insert_values, ';');
        PREPARE stmt FROM @sql; EXECUTE stmt;
        SET end_idx = LEAST(total, start_idx + batch_size - 1);
    END WHILE;
END$$
DELIMITER ;
CALL batchInsertData(1000000);

3. Check Storage Size

SELECT
    table_schema AS "数据库",
    table_name   AS "表名",
    table_rows   AS "记录数",
    TRUNCATE(data_length/1024/1024,2) AS "数据容量（MB）",
    TRUNCATE(index_length/1024/1024,2) AS "索引容量（MB）"
FROM information_schema.TABLES
WHERE table_schema='test_mysql_field'
  AND TABLE_NAME='category_info_varchar_50';

The same query is run for category_info_varchar_500. The resulting screenshots (shown below) indicate that both tables occupy identical storage space.

Validating Performance Difference

1. Index‑Covering Queries

select name from category_info_varchar_50 where name='name100000'; -- 0.012s
select name from category_info_varchar_500 where name='name100000'; -- 0.012s
select name from category_info_varchar_50 order by name; -- 0.370s
select name from category_info_varchar_500 order by name; -- 0.379s

Index‑based lookups show negligible differences.

2. Full‑Table Scans Without Sorting

select * from category_info_varchar_50 where name='name100000'; -- 0.012s
select * from category_info_varchar_500 where name='name100000'; -- 0.012s
select * from category_info_varchar_50 where name in (...); -- 0.011s‑0.014s
select * from category_info_varchar_500 where name in (...); -- 0.012s‑0.014s

Scanning the whole table without ORDER BY yields similar timings.

3. Full‑Table Scans With Sorting

select * from category_info_varchar_50 order by name; -- 1.498s
select * from category_info_varchar_500 order by name; -- 4.875s

When sorting is required, the VARCHAR(500) table is more than three times slower.

Analysis of the Sorting Overhead

Execution plans reveal that the longer VARCHAR causes MySQL to allocate a larger memory estimate for the sort operation. Because the estimate exceeds sort_buffer_size, MySQL falls back to disk‑based temporary tables, dramatically increasing Created_tmp_files and sort_merge_passes.

For the VARCHAR(50) table:

Created_tmp_files = 3

sort_merge_passes = 95

For the VARCHAR(500) table:

Created_tmp_files = 4

sort_merge_passes = 645

Number of merge passes that the sort algorithm has had to do. If this value is large, you may want to increase the value of sort_buffer_size .

Thus, a larger VARCHAR length leads to more merge passes, indicating that the sort buffer is insufficient for the data size.

Final Conclusion

When a VARCHAR column is used in ORDER BY or other sorting operations, MySQL estimates the required memory based on the column’s defined length. Over‑allocating length (e.g., VARCHAR(500) for short strings) can cause the estimate to exceed sort_buffer_size, forcing MySQL to write temporary files to disk and severely degrading query performance, even though storage size remains unchanged.