Impact of VARCHAR Length on MySQL Storage and Query Performance

Problem: When designing a table schema, guidelines suggest using the shortest possible variable‑length field length. This article investigates whether VARCHAR(50) and VARCHAR(500) occupy the same storage and how field length affects query performance.

1. Storage Space Verification

1.1 Prepare 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='分类';

1.2 Insert data

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);

1.3 Check storage

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';

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_500';

Result: Both tables consume identical data and index space.

2. Performance Verification

2.1 Index covering query

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

Covering queries show negligible difference.

2.2 Index range query

select * from category_info_varchar_50 where name in ('name100','name1000',...,'name9900000'); -- 0.011s‑0.014s

select * from category_info_varchar_500 where name in ('name100','name1000',...,'name9900000'); -- 0.012s‑0.014s

Range queries have similar performance; adding ORDER BY introduces a slight gap.

2.3 Full table scan with and without ORDER BY

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

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

Without ORDER BY, both tables run at comparable speed. With ORDER BY, the longer VARCHAR causes a dramatic slowdown because MySQL resorts to disk‑based sorting.

3. Analysis

EXPLAIN output for the varchar_500 query shows more temporary files and a much higher sort_merge_passes count (645 vs 95). This indicates that MySQL’s memory estimate exceeds sort_buffer_size , triggering external merge sort.

4. Final Conclusion

Using excessively long VARCHAR columns does not increase storage size, but it can severely degrade query performance when sorting. MySQL overestimates memory usage based on column length, leading to disk‑based temporary tables and higher sort costs.