Skip navigation links

MySQL Forums :: Performance :: How to improve query performance of a large table


Advanced Search

How to improve query performance of a large table
Posted by: Hong Yang ()
Date: September 05, 2013 11:32AM

There is a nightly batch that summarizes raw data and stores in a summary table. The raw_data table is partition by date, one partition per day based on the fct_ts (timestamp) column value.
The raw_data table is defined as follow:
device_key int(11)PK
component_key int(11) PK
fct_ts timestamp PK
fct_value double

The hourly_summary table contains the summary data for each hour which is also partitioned by date. Table is defined as follow:
device_key int(11) PK
component_key int(11) PK
date_key int(10) UN PK
fct_ts timestamp PK
fct_avg double
fct_min double
fct_max double
fct_count int(11)

Neither table has indexes besides the primary key. The each partition of raw_data table contains 500M rows which are convert into roughly 34M row in the hourly_summary table.

This batch process is accomplished by executing the following query which ran for 8 hours to process one day worth of data.
Is there optimization can be done to speed up the execution?

REPLACE INTO hourly_summary
( device_key, component_key, date_key, fct_ts, fct_avg, fct_min, fct_max, fct_count )
SELECT main.device_key, main.component_key, TO_DAYS(main.fct_ts), CONVERT(TRUNCATE(main.fct_ts,-4), DATETIME) AS tick,
AVG(main.fct_value) AS fct_avg,
MIN(main.fct_value) AS fct_min, MAX(main.fct_value) AS fct_max, COUNT(main.fct_value) AS fct_count
FROM raw_data main
WHERE fct_ts >= TIMESTAMP('2013-09-01 00:00:00')
AND fct_ts < TIMESTAMP('2013-09-02 00:00:00')
GROUP BY fct_ts, device_key, component_key, tick

Options: ReplyQuote


Subject Views Written By Posted
How to improve query performance of a large table 4177 Hong Yang 09/05/2013 11:32AM
Re: How to improve query performance of a large table 1151 Rick James 09/06/2013 08:47AM
Re: How to improve query performance of a large table 929 Hong Yang 09/06/2013 11:49AM
Re: How to improve query performance of a large table 585 Rick James 09/07/2013 08:11AM
Re: How to improve query performance of a large table 490 Hong Yang 09/09/2013 01:29PM
Re: How to improve query performance of a large table 513 Rick James 09/12/2013 09:30PM
Re: How to improve query performance of a large table 440 Hong Yang 09/17/2013 07:35AM


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.