How Many Rows Can a Single MySQL SELECT Return? Exploring max_allowed_packet and Row Size Limits

Problem

A simple select * from user query is asked: can it return all rows when the table holds 100, 10,000, 100,000, or even a million records? Although such full‑table scans are rarely used in production, the question is worth exploring.

Finding the Answer – max_allowed_packet

All tests are based on MySQL 8.

max_allowed_packet

The MySQL documentation shows that the default max_allowed_packet size is 16 MiB for the client and 64 MiB for the server, with an upper bound of 1 GiB.

The maximum size of one packet or any generated/intermediate string, or any parameter sent by the mysql_stmt_send_long_data() C API function.

The term “packet” in this context can refer to a single SQL statement sent to the server, a single row sent to the client, or a binary‑log event sent from a master to a replica.

Single‑Row Maximum Storage

MySQL limits a row’s maximum width to 65,535 bytes (≈64 KB) for both InnoDB and MyISAM, not counting BLOB/TEXT data. Experiments show that using VARCHAR(10000) columns with latin1 (1 byte per character) succeeds, but the same definition with utf8mb3 or utf8mb4 (3‑4 bytes per character) fails because the total byte length exceeds the limit.

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;

When the character set is changed to utf8mb3 or utf8mb4, the same statement errors with “Row size too large”. This is because the row’s fixed‑length part still exceeds 65,535 bytes.

Additionally, a column’s width must be less than half of the InnoDB page size (default 16 KB), i.e., roughly 8 KB.

Row‑Overflow Mechanism

InnoDB supports four row formats: REDUNDANT, COMPACT, DYNAMIC (default), and COMPRESSED. The COMPACT/DYNAMIC formats store the first 768 bytes of variable‑length columns (VARCHAR, VARBINARY, BLOB, TEXT) in‑row and overflow the remainder to separate pages, linking them via a pointer.

If a row’s size exceeds the InnoDB maximum row size, InnoDB moves variable‑length columns to overflow pages until the row fits.

Column Count Limits

A MySQL table can have up to 4,096 columns, but InnoDB restricts this to 1,017 columns.

Experiments

To verify that max_allowed_packet constrains single‑row size, the variable was set to its minimum (1,024 bytes). A table with 32 CHAR(255) columns (total ≈8 KB) was created, and a SELECT * on that table failed with a “Packet Too Large” error, confirming the limit.

SET GLOBAL max_allowed_packet = 1024;
SELECT * FROM t1;
-- Error: Packet Too Large

Further tests showed that the InnoDB row‑size limit is slightly below half a page (≈8 KB) to allow at least two rows per 16 KB page, preserving B‑Tree efficiency.

Answer

There is no fixed numeric answer to “how many rows can a SELECT return?” because the limit depends on several factors:

Maximum single‑row size (cannot exceed max_allowed_packet and 65,535 bytes for fixed‑length data).

InnoDB column count (≤ 1,017) and total column width (≤ 65,535 bytes).

Page‑size constraints (column width must be < ½ page, ~8 KB for the default 16 KB page).

Available server memory, buffer pool size, query timeout settings, and client memory.

In practice, a full‑table scan without a WHERE clause is rarely used in production, and the actual number of rows that can be retrieved is bounded by the above storage limits and the server’s memory configuration.

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://www.cnblogs.com/ZhuChangwu/p/14035330.html

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