MySQL Forums
Forum List  »  Optimizer & Parser

Re: SELECT on multiple column index
Posted by: Sergey Petrunya
Date: December 10, 2004 11:02AM

> As far as I can remember, you can't keep the handler open because it conflicts
> with other connections and insert/update, right?
It does not lock the table, but you may run into some issues listed on the manual page I've mentioned, which may or may not be acceptable depending on your application.

> Why does the above query not use the index? Why can't I force it to? It understands
> the query correctly and the results are consistent.
The problem is with "(keypart1, ..., keypartN) > (c1, ..., cN)" expression. MySQL can evaluate it correctly (hence the correct results) but it cannot recognize that this expression can be used to perform a range scan. Using FORCE INDEX won't help here.

I understand your surprise because conditions like "(keypart1, ... keypartN) > (c1, .. cN)" are natural for BTREE indexes, and this is what MySQL optimizer actually internally uses (and this is why you can use them for handler). Still, currently the optimizer does not recognize that SQL condition "(keypart1, ..., keypartN) > (c1, ..., cN)" can be used to perform a range scan over index.

Sergey Petrunia, Software Developer
My blog:

Options: ReplyQuote

Written By
December 05, 2004 07:18AM
December 09, 2004 09:08AM
December 09, 2004 11:23AM
December 09, 2004 07:38PM
December 10, 2004 07:51AM
December 10, 2004 08:42AM
December 10, 2004 10:23AM
Re: SELECT on multiple column index
December 10, 2004 11:02AM

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.