MySQL Forums
Forum List  »  Optimizer & Parser

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




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.