MySQL Forums
Forum List  »  Optimizer & Parser

Re: Query execution plan
Posted by: Rick James
Date: May 11, 2012 07:16PM

EXPLAIN PARTITIONS is showing that it thinks it needs to look in all the partitions. This is because there is no lower bound on time. Even if there were, your use of DIV probably prevents such an optimization.

It is generally better in RANGE PARTITION to have constant values, not functions (except TO_DAYS, which does not apply in your case).

I see -- on 2008-12-19 02:17. I've learned a lot about PARTITIONing since then.

I am scratching my head over why one EXPLAIN says 1 Rows, the other says a zillion. Maybe EXPLAIN finally takes note of the LIMIT, and this is a clue of fast vs slow. I suspect the change did not happen much before your 5.1.57.

What is 5706114878826086400? Nanoseconds? Is it the top of an hour? I ask because if that is the only kind of query you have (find the last value in an hour), the I would propose not storing all the data, but summarizing it as it comes in. That is, collect the min, max, count, sum, (average = sum/count), first, last, etc over each minute (or other suitable unit) for each channel. All of that would nicely fit in a single table, and all of these performance issues would vanish.
Plan A: Have a Perl script listen for the incoming data; write one row each minute. (One script for all channels, or one per channel -- depending on the what is convenient.)
Plan B: INSERT into a MEMORY table, Summarize the minute's worth of data each minute per channel; TRUNCATE the table. (Do this with a pair of tables ping-ponging. Again, one pair total, or one pair per channel.)

> 93-row table partitioned
OK, it should work. Wasteful, but no big deal.

Options: ReplyQuote

Written By
May 08, 2012 12:38PM
May 09, 2012 11:08AM
May 09, 2012 09:35PM
May 10, 2012 08:29AM
Re: Query execution plan
May 11, 2012 07:16PM
May 14, 2012 05:27AM
May 15, 2012 07:22PM

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.