List:Internals« Previous MessageNext Message »
From:Baron Schwartz Date:January 3 2008 8:23pm
Subject:Re: what should an assert_efficient_sql check for?
View as plain text  
Hi,

On Jan 3, 2008 3:11 PM, Phlip <phlip2005@stripped> wrote:
> Baron Schwartz wrote:
>
> >> Curiously, why would a select-all over 1535 records return 1164 rows?
> >>
> >> Is that just because >300 were already buffered in memory?
> >
> > No, it's just that the 'rows' is an estimate based on storage engine
> > statistics, and is wrong.
>
> I can use it for order-of-magnitude estimates, right? A table with 15 records
> and an EXPLAIN rows of 4,901 is probably an issue with your SQL and tables, not
> with the storage engine, right?
>
> And/or, did FLUSH TABLES fix your "wrong"?
>

I didn't actually test anything when I replied to you, I was just
speaking from memory about how the statistics work.

The stats are usually good to within an order of magnitude on InnoDB,
and usually even better on MyISAM, as long as they are up-to-date.
You can use ANALYZE TABLE to ask the storage engine to refresh its
stats.  FLUSH TABLES has pretty much the same effect because it forces
the table to be closed and then re-opened, at which point the stats
are re-computed.
Thread
what should an assert_efficient_sql check for?Phlip3 Jan
  • Re: what should an assert_efficient_sql check for?Jay Pipes3 Jan
    • Re: what should an assert_efficient_sql check for?Phlip3 Jan
    • Re: what should an assert_efficient_sql check for?Phlip3 Jan
      • Re: what should an assert_efficient_sql check for?Baron Schwartz3 Jan
        • Re: what should an assert_efficient_sql check for?Phlip3 Jan
          • Re: what should an assert_efficient_sql check for?Baron Schwartz3 Jan
      • Re: what should an assert_efficient_sql check for?Phlip3 Jan
Re: what should an assert_efficient_sql check for?Phlip4 Jan