Does Varchar Length Really Impact MySQL Storage and Performance? A Practical Test

Problem Description

When designing table schemas, a rule suggests using the shortest possible variable‑length field length that meets requirements. The article investigates why, focusing on storage space and performance considerations.

Verifying Storage Space Difference

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

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

3. Verify Storage Space

Queries on information_schema.TABLES for each table show identical data and index sizes (see images).

Performance Verification

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

Index‑covering queries show negligible difference.

2. Index Range Query

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 ('name100','name1000',...,'name9900000'); -- 0.011s‑0.014s
select * from category_info_varchar_500 where name in ('name100','name1000',...,'name9900000'); -- 0.012s‑0.014s
-- Adding ORDER BY adds a small overhead (≈0.012s‑0.017s).

Range queries perform similarly; ORDER BY introduces slight extra cost.

3. Full Table Scan and Sorting

Without ORDER BY, both tables have comparable execution time. With ORDER BY, the VARCHAR(500) table is much slower.

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

Final Conclusion

When a variable‑length column is used for sorting, MySQL estimates the required memory based on the column’s defined length. Overly large VARCHAR definitions cause the estimate to exceed sort_buffer_size, forcing MySQL to create disk‑based temporary tables, which dramatically degrades query performance.