MySQL Bugs: #24268: function to determine size of result set

Bug #24268	function to determine size of result set
Submitted:	13 Nov 2006 15:54	Modified:	18 Oct 2008 10:03
Reporter:	Yahoo Serious (Silver Quality Contributor)	Email Updates:
Status:	Open	Impact on me:	None
Category:	MySQL Server: General	Severity:	S4 (Feature request)
Version:	all	OS:	Any (all)
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
I would like to have a SQL-function to determine size of the result set.
I can not imigine it does not exist yet, but I can not find it anywhere (not by a search on "size" or "length", and not in chapter 12 ("Functions and Operators").

Since the optimization of the query-cache depends on knowing the size of your result-set, it would be nice to have an easy way to measure this.  The function may return without actually sending the would-be result set (like "COUNT(*)"), or after sending the result (like "FOUND_ROWS()").

The first method (without result), will probably benefit more programs/programmers.

How to repeat:
feature-request

Suggested fix:
feature-request

Thank you for the feature request. Can you, please, be more specific and suggest exact syntax and provide some use cases with results you expect from this function.

I am sorry, but this question is two years old, and I have been at another job for 1,5 years, not using MySql anymore.  So I do not have specific syntax or examples handy.  But looking at the manual and my question, maybe I can come up with some sensible remarks.

In the documentation, I found:
http://dev.mysql.com/doc/refman/5.0/en/query-cache-configuration.html
---
To control the maximum size of individual query results that can be cached, set the query_cache_limit system variable. The default value is 1MB.
[...] the query cache allocates blocks with a minimum size given by the query_cache_min_res_unit system variable. [...] Depending on the types of queries your server executes, you might find it helpful to tune the value of query_cache_min_res_unit
---
So, in order to tune query_cache_limit and query_cache_min_res_unit, I suppose one should be able to measure the result set.  

For example, I can imagine:

* showing the result set size after sending the result
This could be doen in analogy with FOUND_ROWS, like:
  SELECT SQL_CALC_SET_SIZE A, B FROM C 
followed by
  SELECT LAST_RESULT_SET_SIZE()
This could also be used in the regular interface:
 - the command-line returning the result set size after a SELECT result set (maybe it already did/does?). 
 - the Query Browser returning the result set size, e.g. im the status bar

* showing the result set size without actually sending the result set 
 - a statement to be able to (automatically) gather this data for (a batch of) queries, something like
  SELECT RESULT_SET_SIZE(SELECT A, B FROM C)

This way one can get more insight in the result set size and one would therefore be better equipped to tune query_cache_limit and query_cache_min_res_unit

Hope that helps.

I found this request while searching for a method to determine the resultset size in order to tune the query cache. So it would be really great if this feature gets implemented.

Regards

Henrik

let me put my voice on this function request. i found this page while trying to figure out if mysql has the function to determine query result set size. I know this post (or request) is really old, was this feature ever implemented in any of the latest releases? It would be good to have it we need to accurately set the query_cache_limit. Knowing the result set size provides a guiding value to set the query_cache_limit to know the size of a routine query that need to hit the query cache.