How to get the byte size of resultset in an SQL query?
28,850
Solution 1
select sum(row_size)
from (
select
char_length(column1)+
char_length(column2)+
char_length(column3)+
char_length(column4) ... <-- repeat for all columns
as row_size
from your_table
) as tbl1;
char_length
for enum
, set
might not accurate, please take note
Solution 2
To build on Angelin's solution, if your data contains nulls, you'll want to add IFNULL to each column:
select sum(
ifnull(char_length(column1), 0) +
ifnull(char_length(column2), 0) +
ifnull(char_length(column3), 0) +
ifnull(char_length(column4), 0) ... <-- repeat for all columns
)
from your_table
Solution 3
simplify :
select sum(char_length(column1)+
char_length(column2)+
char_length(column3)+
char_length(column4) ... )<-- repeat for all columns
from your_table
You need to add IFNULL() to each column as @futilerebel has mentioned
Solution 4
CHAR_LENGTH()
gets number of characters if unicode will be more bytes - use LENGTH()
for number of bytes:https://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_length
Author by
Raj
Updated on July 09, 2022Comments
-
Raj almost 2 years
Is it possible to get the size in bytes of the results of an sql query in MySQL?
For example:
select * from sometable;
ths returns 10000 rows. I don't want the rows but the size of the resultset in bytes. Is it possible?