MySQL Forums
Forum List  »  General

Re: Large Table structure recommendations
Posted by: Peter Brawley
Date: February 20, 2020 10:21AM

First, do understand the query correctly... it pivots on enum values to average several param values joined to max hourly values for one param, is that right?

On first inspection it looks like a variant of the "within-group aggregates" query pattern ( Some solutions to that problem are a lot faster than others, and different solutions imply somewhat different table designs. I'd want to benchmark them all against a range of table designs and data volumes so I can pick the best Explain Analyze result and the best performance curve.

Pivot queries are slow and don't scale well. I'd probably start by replacing the param enum column with a tinyint column referencing a lookup table for the param strings.

And since 8.0 offers tgols that are likely to help, (windowing functions, CTEs, other optimisations), I'd upgrade to 8.0 before doing anything else.

Options: ReplyQuote

Written By
Re: Large Table structure recommendations
February 20, 2020 10:21AM

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.