MySQL Forums
Forum List  »  Optimizer & Parser

Re: Statistics used by optimizer.
Posted by: Rick James
Date: August 29, 2009 01:11PM

MyISAM and InnoDB get their statistics different ways:
MyISAM always maintains COUNT(*) for the table; it uses ANALYZE TABLE to get some statistics for cardinality of indexes.
InnoDB probes 10 random spots in the table whenever you 'open' the table. This is fast, but can be inadequate for a good query plan choice.

When optimizing a particular SELECT, with particular values in the WHERE clause, MySQL (both engines) will look into the index to get a better feel for the cardinality for the particular value. It will use the information gleaned to pick the 'best' query plan. Different values can lead to different query plans. This might be an example of such:
CREATE TABLE x
...
   INDEX (a), -- a is usually 0; a = 999 once
   INDEX (b), -- b is usually 0; b = 999 once
);
SELECT ... WHERE a=0 AND b=999; -- will use INDEX(b)
SELECT ... WHERE a=999 AND b=0; -- will use INDEX(a)

(I won't delve into the theory of histograms.)

Options: ReplyQuote


Subject
Views
Written By
Posted
6358
August 28, 2009 07:32AM
Re: Statistics used by optimizer.
2643
August 29, 2009 01:11PM
2497
September 01, 2009 06:30AM
2448
September 01, 2009 07:44AM
2474
September 01, 2009 02:51PM
2277
September 01, 2009 09:59PM
2406
September 04, 2009 11:00AM
2403
September 04, 2009 07:37PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.