MySQL Forums
Forum List  »  Performance

Re: How to improve query performance of a large table
Posted by: Rick James
Date: September 12, 2013 09:30PM

It is touching only the one partition -- good.
It is touching the entire partition -- time consuming.
The WHERE clause asks to touch the entire partition.

> Basically every row in a particular partition will need to be processed. Would it perform better instead of processing everything all at once, divide the data into multiple trunks and process one chunk at a time?

No, rearranging/splitting/etc. won't help. "Count the disk hits" -- you have to touch all 40GB; that is what takes the time.

But... What is the problem? That the query takes a long time? Or that the query finishes after a long time? There is a subtle difference. If you summed up each hour after it came in, it would take the same amount of time overall, but you would be finished sooner. Instead of finishing at 08:00, it might finish about 00:20.

To do that...
PRIMARY KEY (`fct_ts`,`device_key`,`component_key`) -- good.
AVG --> SUM and COUNT; then the average is SUM(sums)/SUM(counts)

I suggest you consider doing the summarization, then tossing the raw data. Or do you have some use for the raw data?

Describe things further; I suspect there are more issues we have not discussed yet.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: How to improve query performance of a large table
1272
September 12, 2013 09:30PM


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.