Impact of VARCHAR Length on MySQL Storage and Query Performance

Problem Description – Database design guidelines often recommend using the shortest possible variable‑length field. The article asks whether a VARCHAR(50) and a VARCHAR(500) column really consume the same storage and whether longer definitions degrade query performance.

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 One Million Rows into Both Tables

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 Difference

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'
ORDER BY data_length DESC, index_length DESC;

Running the same query for category_info_varchar_500 shows that both tables occupy virtually the same amount of disk space.

4. Performance Tests

Index‑covering SELECT (no ORDER BY)

select name from category_info_varchar_50 where name = 'name100000';  -- 耗时0.012s
select name from category_info_varchar_500 where name = 'name100000'; -- 耗时0.012s

Full table scan without ORDER BY

select * from category_info_varchar_50 where name = 'name100000';  -- 耗时0.012s
select * from category_info_varchar_500 where name = 'name100000'; -- 耗时0.012s

Full table scan with ORDER BY

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

The results indicate that when sorting is required, the table with the longer VARCHAR definition is significantly slower because MySQL allocates a larger in‑memory buffer for the column, causing the sort to spill to disk.

5. Analysis of Execution Plans

For the varchar(50) table, the EXPLAIN output shows that about 86 % of the time is spent on data transmission, with only a few temporary files created (Created_tmp_files = 3) and a modest number of merge passes (sort_merge_passes = 95).

For the varchar(500) table, the same query creates more temporary files (Created_tmp_files = 4) and many more merge passes (sort_merge_passes = 645), confirming that the larger column length forces MySQL to use disk‑based temporary tables for sorting.

MySQL documentation states that a high sort_merge_passes value suggests increasing sort_buffer_size or reducing the amount of data that needs to be sorted.

Final Conclusion

When a VARCHAR column is used in ORDER BY or other operations that require sorting, an excessively large declared length leads MySQL to over‑estimate the required memory, exceed sort_buffer_size , and fall back to disk‑based sorting, dramatically degrading query performance. Therefore, choosing the shortest appropriate VARCHAR length is important for both storage efficiency and query speed.