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
MySQL AB, www.mysql.com
My blog: http://s.petrunia.net/blog

Options: ReplyQuote


Subject
Views
Written By
Posted
9459
December 05, 2004 07:18AM
3302
December 09, 2004 09:08AM
3374
December 09, 2004 11:23AM
3000
December 09, 2004 07:38PM
3208
December 10, 2004 07:51AM
4270
December 10, 2004 08:42AM
2949
December 10, 2004 10:23AM
Re: SELECT on multiple column index
3352
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.