> 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