Get MySQL result-set size from query

Question

Is it possible to get the size of the result-set when doing a query?

I need to set a proper MySQL cache_limit (MB) and therefore I am trying out some queries, but I need to know the sizes of the result-sets to fine-tune my cache configurations.

What exactly does query_cache_limit do when measuring the size of a query (or result)...

Answer 1

There are several ways to measure the size of data sent from your mysql server to your php process:

Pure SQL

Do your query and execute SHOW SESSION STATUS directly after it. You will get several statistics, including the sent and received bytes:

Bytes_received  191
Bytes_sent      120

Subtract the bytes from a SHOW SESSION STATUS query alone, and you have the exact values.

mysqlnd

PHP 5.3 offers the "MySQL Native Driver", which gives you some nice options to debug your connection.

Do your query and then call mysqli_get_connection_stats. It returns the network statistics, too:

Array
(
    [bytes_sent] => 43
    [bytes_received] => 80
    ...

You need to use mysqli and mysqlnd here, but you get more accurate numbers as with the pure SQL solution.