Does Varchar Length Really Affect MySQL Storage and Performance?

Problem Description

When designing table schemas, a rule states that for variable‑length fields we should use the shortest possible length that satisfies requirements. The article explores why this rule exists, focusing on storage and performance considerations.

Verifying Storage 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. Measure storage

Query the information_schema for each table:

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 result shows identical data and index sizes for both tables.

... same query for category_info_varchar_500 ...

Again, no storage difference is observed.

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‑only scans have similar latency.

2. Range query

select * from category_info_varchar_50 where name = 'name100000';   -- 0.012s
select * from category_info_varchar_500 where name = 'name100000';   -- 0.012s
... (additional IN‑list queries) ...

Range queries show comparable times; adding ORDER BY introduces a slight slowdown.

3. Full‑table scan 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 the entire result set, the varchar(500) table is dramatically slower.

Execution plans reveal that the longer column forces MySQL to create temporary files for sorting, increasing Created_tmp_files and sort_merge_passes. The following blockquote explains sort_merge_passes and suggests increasing sort_buffer_size to mitigate the issue.

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 the sort_buffer_size.

Final Conclusion

Both varchar(50) and varchar(500) consume the same amount of disk space, but when the column participates in ORDER BY operations MySQL estimates memory usage based on the defined length. Over‑allocating variable‑length fields can exceed sort_buffer_size, causing disk‑based temporary sorting and significantly degrading query performance.

Source: juejin.cn