MySQL Forums
Forum List  »  Performance

Re: MySQL 5.1.49 - Optimal settings for memory usage
Posted by: Rick James
Date: January 17, 2012 11:44PM

SELECT Win.time,
       UNIX_TIMESTAMP(Win.time) AS unixtime,
       YEAR(Win.time) AS jahr, MONTH(Win.time) AS monat,
       DAY(Win.time) AS tag, HOUR(Win.time) DIV 12 AS intervall,
       ROUND(AVG(SIN(Win.windricht/180*PI())), 4) AS avg_sin_windricht,
       ROUND(AVG(COS(Win.windricht/180*PI())), 4) AS avg_cos_windricht,
       ROUND(AVG(Win.windgeschw), 4) AS avg_windgeschw,
       ROUND(AVG(-ABS(Win.windgeschw)*SIN(Win.windricht/180*PI())), 4) AS avg_uwind,
       ROUND(AVG(-ABS(Win.windgeschw)*COS(Win.windricht/180*PI())), 4) AS avg_vwind
    FROM  MIUB_messdaten.Wind AS Win
    WHERE  Win.time>='2011-01-17 07:54:00'
      AND  Win.time<'2012-01-17 07:54:00'
      AND  Win.Geraete_idGeraete=2
    GROUP BY  jahr,monat,tag,intervall
    ORDER BY  Win.time;
Needs an index on (Geraete_idGeraete, time) in that order. Since it did not use INDEX(Geraete_idGeraete), I have to assume that many rows have Geraete_idGeraete=2. Is that the case?

Because this is InnoDB, INDEX(Geraete_idGeraete) has `time` implicitly tacked on the end (so that it can find the PRIMARY KEY).

Swapping the keys to
PRIMARY KEY(Geraete_idGeraete, time),
INDEX(time)
would take some time, and may or may not speed up that query.

It would help to have the ORDER BY be the same as the GROUP BY.

If you don't really need jahr, monat, etc, then you could work with
FLOOR(UNIX_TIMESTAMP(`time`) / (12*60*60))
except there might be a timezone issue.

The other approach is to create a "Summary table" that pre-calculates these values. Then use that table for this query. The new table would be updated every 12 hours with a new row. The resulting query would run probably more than 100x faster.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: MySQL 5.1.49 - Optimal settings for memory usage
1519
January 17, 2012 11:44PM


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.