How to calculate size from my query MySQL?

Question

I try to find size in MB of my query, but I don't understand how to do it. I would like to extract the size of my query in this way:

SELECT size_query FROM my_query

Where 'my_query' it could be: SELECT * FROM MyTable WHERE idFactory = 1

Answer 1

If I understand your question correctly, ajreal has already provided a solution on this StackOverflow question. Quoted:

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;

This will give you the size of your query in bytes, divide by 1024 for kilobytes and again for megabytes.

Edit: If you're pulling the full result set back to PHP anyway and want to know the size of it, you could calculate it in PHP using something like this:

<?php

$data = [
    [
        'item' => 'Apple',
        'type' => 'fruit',
        'in_stock' => true
    ],
    [
        'item' => 'Biscuits',
        'type' => 'confectionery',
        'in_stock' => false
    ],
    [
        'item' => 'Milk',
        'type' => 'dairy',
        'in_stock' => true
    ],
];


function get_array_size(&$array)
{
    $size = 0;

    foreach ($array as $key => $value) {
        if (is_array($value)) {
            $size += get_array_size($value);
        } else if (is_string($value)) {
            $size += strlen($value);
        } else if (is_bool($value)) {
            $size += 1;
        } /* else if ( some other type ) {

        } */
    }

    return $size;
}

echo get_array_size($data); // Outputs 43

This may or may not be acceptable to you depending on your use case. If you're looking to measure the physical bytes on the wire, this probably won't be accurate enough.