Skip navigation links

MySQL Forums :: Optimizer & Parser :: Identical servers different plan for the same query


Advanced Search

Re: Identical servers different plan for the same query
Posted by: Eric Coll ()
Date: April 12, 2012 03:23PM

The data comes roughly in chronological order. Probably, good enough for a sequential key.

Those the autoincrement id as a primary key works with a partitioned table?

We insert the data ourselves from unique feeds identified by timestamp, region. So, I could create a timestamp, region, sequence primary key. The advantage of that key is that prevents duplicates records (a recurrent problem in the past that the very long unique key was trying to avoid)

Would that be a good choice for a PK?

This is a simplified version of the double group by query (takes about 3 min, mostly writing to a temp table):

select date(from_unixtime(timestamp)) as day, account, instance_type, ..., max(count) as max
from
( select timestamp, account, zone as internal_az, instance_type, ..., sum(s_total) as count
from
capacity.reservation
where
timestamp between unix_timestamp('2012-03-10') and unix_timestamp('2012-04-10') and
account in ('833008473584')
group by timestamp, account, internal_az, instance_type, ...
) sum
group by day, account, internal_az, instance_type, ...;

The query plan for the inner query that takes most of the time:
+----+-------------+-------+--------+-----------------------------------------------------+------------------------------+---------+-------------------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-----------------------------------------------------+------------------------------+---------+-------------------------+-------+----------------------------------------------+
| 1 | SIMPLE | r | range | idx_reservation_unique,idx_reservation_account_time | idx_reservation_account_time | 70 | NULL | 58055 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | pt | eq_ref | PRIMARY | PRIMARY | 4 | capacity.r.pool_type_id | 1 | |
+----+-------------+-------+--------+-----------------------------------------------------+------------------------------+---------+-------------------------+-------+----------------------------------------------+
2 rows in set (0.00 sec)

Thank you!

Options: ReplyQuote


Subject Views Written By Posted
Identical servers different plan for the same query 1515 Eric Coll 04/07/2012 01:20AM
Re: Identical servers different plan for the same query 644 Eric Coll 04/07/2012 09:55AM
Re: Identical servers different plan for the same query 637 irek kordirko 04/07/2012 02:54PM
Re: Identical servers different plan for the same query 840 Rick James 04/08/2012 12:20PM
Re: Identical servers different plan for the same query 853 Eric Coll 04/09/2012 10:24AM
Re: Identical servers different plan for the same query 601 Eric Coll 04/09/2012 10:35AM
Re: Identical servers different plan for the same query 608 Eric Coll 04/09/2012 10:37AM
Re: Identical servers different plan for the same query 583 Eric Coll 04/09/2012 10:42AM
Re: Identical servers different plan for the same query 580 Eric Coll 04/10/2012 03:28PM
Re: Identical servers different plan for the same query 607 Rick James 04/10/2012 10:29PM
Re: Identical servers different plan for the same query 606 Eric Coll 04/11/2012 10:05AM
Re: Identical servers different plan for the same query 640 Rick James 04/12/2012 08:56AM
Re: Identical servers different plan for the same query 601 Eric Coll 04/12/2012 03:23PM
Re: Identical servers different plan for the same query 627 Rick James 04/13/2012 03:49PM


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.