MySQL Forums
Forum List  »  Partitioning

Re: Range partition ordering for ORDER BY LIMIT queries
Posted by: Mattias Jonsson
Date: February 22, 2011 05:22AM

Just try it using "FLUSH STATUS" before the call, and then "SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0"

That way you can see what operations was done through the handlers (most statistics are only from the partitions handlers).

And regarding 'date', it must be a part of the primary key if it exists, otherwise it cannot be partitioned.

Ordered retrieval through the partitioning engine are implemented by read the 'first' row from each matching partition ('first' depends on ASC/DESC) and place them into a sorted queue. Then return the 'first' row from the queue.
The next retrieval will retrieve the 'next' row from the previously used partition, and then again return the 'first' row from the queue.

So this particular LIMIT with DESC optimization is not implemented within the generic partitioning storage engine.

Please feel free to report a bug/feature request on this, and add a comment on which bug no here.

Regards
Mattias Jonsson

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Range partition ordering for ORDER BY LIMIT queries
1328
February 22, 2011 05:22AM


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.