MySQL Forums
Forum List  »  Optimizer & Parser

Re: Retrieved columns affects index usage
Posted by: KimSeong Loh
Date: February 13, 2007 06:37PM

The optimiser has to use some assumptions since it does not know the exact data in the table until it executes the statement. The 1/2 or 2/3 data is unkown to the optimiser. The only available information to the MySQL optimiser is the cardinality, number of distinct value in the index, and if I remember correctly, even this is not exactly accurate with InnoDB.

You can try force_index in special situation like this.

But, if it is going to read 1/3 of the data, a full table scan might be faster than index scan sometimes.

As for
SELECT AColumn FROM ATABLE WHERE NOT (AColumn='')
My guess, it only need to read the index without having to read the data itself, this is a covering index, so it may decide to read the index, and since '' is the smallest value in the column, it can actually make it AColumn>'' and use a range on the index, otherwise it could be "index"

Options: ReplyQuote


Subject
Views
Written By
Posted
2604
February 13, 2007 12:06PM
Re: Retrieved columns affects index usage
1901
February 13, 2007 06: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.