How Many Rows Can a Single MySQL Query Return? Exploring Packet and Row Size Limits

Problem Overview

When executing a statement such as SELECT * FROM user, the number of rows returned is not limited by a fixed numeric value. Instead, the ability to return all rows depends on MySQL configuration, especially the max_allowed_packet setting, and on internal row‑size limits.

max_allowed_packet

In MySQL 8 the system variable max_allowed_packet controls the maximum size of a communication packet. The defaults and limits are:

Client default: 16 MiB (cannot exceed 1 GiB).

Server default: 64 MiB.

Maximum configurable value: 1 GiB (must be a multiple of 1024).

The packet definition applies to three cases:

A single SQL statement sent to the server.

A single row sent from the server to the client.

A binary‑log event transmitted between replication peers.

Single‑Row Size Limits

MySQL limits the maximum width of a row to 65 535 bytes (≈64 KB) for both InnoDB and MyISAM. This limit excludes BLOB and TEXT columns, which are stored off‑page.

When a VARCHAR column exceeds 768 bytes, MySQL stores the first 768 bytes in‑row and overflows the remainder to separate pages (row‑overflow). Row formats in InnoDB (REDUNDANT, COMPACT, DYNAMIC, COMPRESSED) affect how variable‑length columns are stored; the default is DYNAMIC.

Column Count and Page‑Size Constraints

Maximum columns per table: 4 096 (InnoDB limits to 1 017).

For fixed‑length columns the total length must be less than half of the InnoDB page size (default 16 KB → roughly 8 KB per row).

The sum of defined column lengths must not exceed 65 535 bytes.

Experiments

Creating a Wide Table

CREATE TABLE t (
  a VARCHAR(10000), b VARCHAR(10000),
  c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
  f VARCHAR(10000), g TEXT(6000)
) ENGINE=InnoDB CHARACTER SET latin1;

The table is created successfully because TEXT columns are off‑page and the latin1 charset uses one byte per character.

Row‑Size Error Example

CREATE TABLE t4 (
  c1 CHAR(255), c2 CHAR(255), … , c33 CHAR(255)
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET=latin1;

This statement fails with error 1118: “Row size too large (> 8126)”. Reducing the total length to 8 097 bytes succeeds, showing that hidden metadata also consumes space.

Procedure to Test max_allowed_packet

CREATE DEFINER=root@`%` PROCEDURE generate_test_data()
BEGIN
  DECLARE i INT DEFAULT 0;
  DECLARE col_value TEXT DEFAULT REPEAT('a',255);
  WHILE i < 5 DO
    INSERT INTO t1 VALUES (col_value, col_value, col_value, col_value, REPEAT('b',192));
    SET i = i + 1;
  END WHILE;
END;

After creating the procedure, set a small packet size: SET GLOBAL max_allowed_packet = 1024; Running SELECT * FROM t1 now produces an error because a single row exceeds the 1 KB packet, confirming that max_allowed_packet constrains row size.

Key Findings

The size of a single row cannot exceed max_allowed_packet.

InnoDB tables support at most 1 017 columns (MySQL overall limit 4 096).

Fixed‑length column definitions must occupy less than half of the InnoDB page size (≈8 KB for the default 16 KB page).

The total defined row length must stay under 65 535 bytes.

When these constraints are satisfied, SELECT * FROM table can return all rows; the practical limit is then the server’s memory, cache sizes, query timeout settings, and client resources.

References

https://dev.mysql.com/doc/refman/8.0/en/packet-too-large.html

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_max_allowed_packet

https://dev.mysql.com/doc/refman/8.0/en/column-count-limit.html