MySQL Forums
Forum List  »  Optimizer & Parser

Follow up on "Query Execution Plan"
Posted by: Chris Slominski
Date: October 24, 2012 11:59AM

I posted a topic about five months ago, but I now see it is closed. I want to add some additional information in case anyone is interested. The following link takes you to the original post.

http://forums.mysql.com/read.php?115,532367,532367#msg-532367

As discussed before, I implemented a work around that is only somewhat satisfactory; using an in general slower more complex query to get rid of those rare catistrophic execution times.I decided to revisit this topic and discovered something interesting.

I chose a table that was behaving as a "slow fetch", using the original implementation. As a reminder, the query is as follows.

select * from <table> where time <= <time> order by time desc limit 1

I noticed that the choice of execution plan depended on the value of time supplied in the query. I wrote a test program to ask the server for the execution plan of the query above, but using a time ranging from the begining of data to the end. Remember that the data table rows are timestamped value samples of experimental data, with the time as primary index.

The output of the test program was the time used in the "explain", converted to human friendly for the test output, and the execution plan chosen by the server. My time step for this test was one day. The result was that the execution plan started with 'range' and continued to a point in time where it switched to 'index'; continuing with the latter until the end. I have included the result below, but replaced most of the redundant lines with the '...'.

2009-11-11 03:59:17 range
2009-11-12 03:59:17 range
2009-11-13 03:59:17 range
...
2012-01-07 03:59:17 range
2012-01-08 03:59:17 range
2012-01-09 03:59:17 range
2012-01-10 03:59:17 index
2012-01-11 03:59:17 index
2012-01-12 03:59:17 index
...
2012-10-22 04:59:17 index
2012-10-23 04:59:17 index
2012-10-24 04:59:17 index

The server's choice of plan depends on where the requested time falls within the table index. Fetching older data uses the 'range' plan and is much faster.



Edited 1 time(s). Last edit at 10/25/2012 06:10AM by Chris Slominski.

Options: ReplyQuote


Subject
Views
Written By
Posted
Follow up on "Query Execution Plan"
2424
October 24, 2012 11:59AM


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.