How to get the byte size of resultset in an SQL query?

Question

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?

Pironi

Just curious, why do you need this? – Urushiol

To find out how many rows to fetch and process in a background process. I am in an environment where the memory is limited and fetching too many rows will crash the process. I am trying to process as many rows as possible as allowed by the memory limit. – Pironi

And what can information about the data size was give to you? If you fit into the memory - then it is ok, if you don't - then you'll never get the results about how big the data was. You cannot predict the size of the data will be returned. – Urushiol

We need a byte-accurate count of all rows in the resultset because we bill clients on the amount of data stored on their behalf. For various reasons, an approximation is not sufficient. It'd be nice if we could do this directly using a MySQL function/sproc. – Nucleoprotein

Answer 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

Kennie

You mentioned enum and set not being accurate, how about other types? For example, int can be represented as a string with length varying from 1 to 10, but it always takes 4 bytes in MySQL. Also, following this argument, can one assume that for datasets large enough char_length will always overestimate the total size? – Sidwell

Note: Your result will be NULL if any of your columns are empty. Consider summing only columns you know have a length or use if/else to replace empty columns with zero so you avoid any NULL values. – Skurnik

Answer 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

Marietta

Answer 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

Bloodyminded

Answer 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

Chatelain