Re: mean_rec_length

[Sergei Golubchik]

Hi!

On Jun 12, Kathryn Steinbrink wrote:
> 
> Hello,
> This post regards the mean_rec_length that is returned by a storage engine
> in the info() method.  The returned value is expected to be the 'average
> record length for the table'.
...
> Looking at the MySQL source, it appears that sql_select.cc uses the
> mean_rec_length for caching purposes during joins, but I don't understand
> the full scope of its usage.

It is used to estimate the amount of memory a record of this table will
need in a join cache (or, in other words, how many records will fit in a
given size of join cache).

The estimation is used by the optimizer to estimate the amount of work
it'll need to do for filling up the join cache.

> Can you please advise me on what value to return for the mean_rec_length?
> Is it better for my storage engine to return the fixed length of the
> record, the maximum record length, or something else?

Well, as you understand from the above, the closer your mean_rec_length
is to the average, the better the estimation (of the join cache cost)
will be.

If you'd like you can maintain the average row length all the time
(it's not that difficult, because the value doesn't need to be exact,
you can cut a lot of corners by allowing it to be approximate).

If you'll return the length of the fixed part of the record, join cache
will appear to the optimizer to be cheaper than it really is. If you'll
return the maximum record length - it'll look much more expensive than
it is.

Regards / Mit vielen Grüssen,
Sergei

-- 
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik <serg@stripped>
 / /|_/ / // /\ \/ /_/ / /__  Principal Software Engineer/Server Architect
/_/  /_/\_, /___/\___\_\___/  Sun Microsystems GmbH, HRB München 161028
       <___/                  Sonnenallee 1, 85551 Kirchheim-Heimstetten
Geschäftsführer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer
Vorsitzender des Aufsichtsrates: Martin Häring