CODEWITHSUNDEEP
sqlmysqlrdbms
Raj
Raj

Reputation: 3061

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

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?

Upvotes: 28

Views: 36713

Answers (5)

Jarod Denison
Jarod Denison

Reputation: 21

Save your time, use CONCAT_WS

SELECT
  SUM(CHAR_LENGTH(CONCAT_WS('', column1, column2, column3, column4))) as bsize
FROM table1

Upvotes: 0

Angelin Nadar
Angelin Nadar

Reputation: 9300

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

Upvotes: 11

GrahamB
GrahamB

Reputation: 91

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

Upvotes: 9

futilerebel
futilerebel

Reputation: 343

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

Upvotes: 15

ajreal
ajreal

Reputation: 47311

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

Upvotes: 26

Related Questions