From: Date: January 3 2008 9:23pm Subject: Re: what should an assert_efficient_sql check for? List-Archive: http://lists.mysql.com/internals/35260 Message-Id: <4cfa0b030801031223h7afc0372j43cf9884b3ec225c@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Hi, On Jan 3, 2008 3:11 PM, Phlip 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.