MySQL Forums
Forum List  »  Data Warehouse

Re: Performance problem in InnoDB
Posted by: Rick James
Date: December 02, 2010 12:44AM

How much RAM? (I assume at least 16GB.)

Plz do SHOW CREATE TABLE for the two DIM tables. They need indexes for
subnetDim.PIDType = 'LOCAL'
b2bDim.customer_name = 'turktel'
to have any hope of optimizing.

Which of those is more selective? Add an index to the FACT table for the _key needed to come from the DIM table.

I see no benefit in PARTITIONing for this one SELECT. I assume from
"Comment: Stores the Subnet Tracker hourly data for 1 month "
that you are using PARTITIONing to quickly purge data once a day.

group by fact.date_key , fact.time_key;
It probably does not matter here, but it is usually better to have a single DATETIME or TIMESTAMP field, not a pair of fields.

About the only use for
KEY `idx_FACT_HOUR_SUBNET_TRACKER_lookup` (`creation_time`,`subnet_key`,`time_key`,`date_key`,`b2bunit_key`)
is if you have a query with creation_time in the WHERE or ORDER BY clause.

Or is creation_time the same as date_key+time_key? If so, the GROUP BY creation_time.

What kinds of numbers need decimal(10,2) ? That's numbers up to 99,999,999.99

Options: ReplyQuote

Written By
November 29, 2010 08:03AM
Re: Performance problem in InnoDB
December 02, 2010 12:44AM
December 08, 2010 02:46AM

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.