Does Varchar Length Affect MySQL Storage and Query Performance? A Practical Test

Problem Description

Database design guidelines often advise using the shortest possible variable‑length field. The article asks two questions: does a longer VARCHAR definition (e.g., VARCHAR(500)) actually consume more storage than a shorter one (e.g., VARCHAR(50)) and does it affect query performance?

Experiment Setup

Two tables with identical schemas were created, differing only in the length of the name column. A stored procedure batchInsertData inserts 1,000,000 rows into each table.

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

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

Storage Comparison

Using information_schema.TABLES the data and index size of both tables were queried. The results (shown in the images) indicate that the two tables occupy exactly the same amount of space.

Performance Tests

Several queries were executed on both tables:

Index‑covering SELECT (retrieving only the indexed column)

Index range SELECT (with IN list)

Full table scan without ORDER BY

Full table scan with ORDER BY on the name column

Timing results (in seconds) are shown below.

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

-- Index range query
SELECT * FROM category_info_varchar_50 WHERE name IN (...); -- 0.011‑0.014s
SELECT * FROM category_info_varchar_500 WHERE name IN (...); -- 0.012‑0.014s

-- Full scan without ORDER BY
SELECT * FROM category_info_varchar_50; -- similar time
SELECT * FROM category_info_varchar_500; -- similar time

-- Full scan with ORDER BY name
SELECT * FROM category_info_varchar_50 ORDER BY name; -- 1.498s
SELECT * FROM category_info_varchar_500 ORDER BY name; -- 4.875s

The index‑covering and range queries show negligible differences, but the ordered full‑table scan on the VARCHAR(500) table is more than three times slower.

Root Cause Analysis

When MySQL needs to sort a column, it estimates the required memory based on the column’s defined length. A larger VARCHAR leads to a larger memory estimate, which can exceed sort_buffer_size. MySQL then falls back to disk‑based temporary tables, increasing Created_tmp_files and sort_merge_passes. The execution plans and EXPLAIN output (shown in the images) confirm that the VARCHAR(500) query creates more temporary files and performs many merge passes, causing the slowdown.

For the VARCHAR(500) table, Created_tmp_files increased to 4 and sort_merge_passes rose to 645, indicating many merge passes during sorting. MySQL’s documentation explains that a high sort_merge_passes value suggests increasing sort_buffer_size or reducing the size of the values being sorted.

Final Conclusion

The length of a VARCHAR column does not affect the amount of physical storage used, but it can dramatically impact query performance when the column participates in sorting. Choosing an appropriate length avoids excessive memory estimation, prevents temporary‑file sorting, and keeps query execution fast.