Do VARCHAR(50) and VARCHAR(500) Really Use the Same Space? MySQL Storage & Performance Test

Problem Description

When designing table schemas, a rule suggests using the shortest possible VARCHAR length for variable‑length columns to save space and improve performance. The article investigates two questions: whether VARCHAR(50) and VARCHAR(500) occupy the same storage, and whether a longer column hurts query performance.

Storage Test

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

-- Same query with TABLE_NAME='category_info_varchar_500'

Result screenshots show that both tables occupy identical space.

Performance Test

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‑covering queries show negligible difference.

2. Full‑table scans with WHERE

select * from category_info_varchar_50 where name='name100000'; -- 0.012s
select * from category_info_varchar_500 where name='name100000'; -- 0.012s
-- Range queries also show similar timings

Full scans with filters have similar performance.

3. 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

A large performance gap appears when sorting the whole table.

Root Cause Analysis

The sort operation creates temporary files when the estimated memory needed exceeds sort_buffer_size. With VARCHAR(500) the optimizer over‑estimates memory, causing MySQL to spill to disk, which dramatically slows the ORDER BY.

Performance‑schema data reveal that 86 % of the time is spent on data transfer, and variables Created_tmp_files and sort_merge_passes increase sharply for the 500‑character column (e.g., Created_tmp_files = 4, sort_merge_passes = 645 versus 3 and 95 for the 50‑character column).

MySQL documentation states that a large sort_merge_passes indicates many merge passes in the sort algorithm; increasing sort_buffer_size or reducing the amount of data per key can reduce these passes.

Final Conclusion

Both VARCHAR(50) and VARCHAR(500) consume the same physical storage for identical data, but a longer VARCHAR dramatically degrades performance for queries that require sorting the whole table because MySQL’s memory estimation leads to disk‑based temporary sorting.