MySQL Forums
Forum List  »  Optimizer & Parser

Re: Statistics used by optimizer.
Posted by: Rakshit Trivedi
Date: September 04, 2009 11:00AM

Hi,

I had one more query. the filter column in the explain shows the fraction of the rows required to be examined. Now I have following select query on a table having an index on attribute X. The cardinality of the table is 2,00,000.

Query:
explain extended
select * from t1 where x<= const;

Now when the value of constant is the one which would fetch the result with cardinality less than around 30% (this is also not exact), index scan is used and rows shows the estimated no. of rows with filter value 100. This is fine.

But when the constant is such that would fetch more than 30% of the data into the result table, the strategy changes to ALL with rows showing the value of the full cardinality of table. At this jucture, the actual role of filter column comes right?? and it should describe what fraction of those rows are actually required to be examined.

But I have observed that for any value of constant which may fetch the data more than 30% i.e. ranging from 30% to full table, the filter value always remains 50, which means the optimizer estimates that it requires half of the table to be read whatever the constant is when the cardinality of the result is going to be more than 30%.. Isn't there some problem here??

What is the reason for the same value of estimation over 30% selectivity??

Options: ReplyQuote


Subject
Views
Written By
Posted
6358
August 28, 2009 07:32AM
2644
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
Re: Statistics used by optimizer.
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.