27

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?

Ram Sharma
  • 8,676
  • 7
  • 43
  • 56
Raj
  • 3,051
  • 6
  • 39
  • 57
  • Just curious, why do you need this? – zerkms Dec 24 '10 at 04:10
  • 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. – Raj Dec 24 '10 at 04:12
  • 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. – zerkms Dec 24 '10 at 04:18
  • 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. – Mike Gleason Aug 15 '18 at 16:23

4 Answers4

24
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

ajreal
  • 46,720
  • 11
  • 89
  • 119
  • 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? – iloo Jan 16 '17 at 12:07
  • 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. – Matthew May 10 '18 at 16:38
14

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
futilerebel
  • 333
  • 1
  • 4
  • 10
11

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

Angelin Nadar
  • 8,944
  • 10
  • 43
  • 53
9

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

Ram Sharma
  • 8,676
  • 7
  • 43
  • 56
GrahamB
  • 91
  • 1
  • 2