967851 - Are these SQL statements optimized?

Status: RESOLVED FIXED

(Data & BI Services Team :: DB: MySQL, task)

Description

[Armen [:armenzg]]

Thank you!

select year(builds.starttime) as Year, month(builds.starttime) as Month, round(sum(unix_timestamp(builds.endtime)-unix_timestamp(builds.starttime))) as "Total CPU sum", count(*) as Jobs, round(sum(unix_timestamp(builds.endtime)-unix_timestamp(builds.starttime))) DIV count(*) as Ratio from builds left join (slaves, builders) on (builds.slave_id=slaves.id and builds.builder_id=builders.id) where builds.starttime > '2013-01-01' and builds.endtime < '2014-01-31' and slaves.name like "t-w732-%" group by Year, Month order by Year, Month;

select builders.name, round(avg(unix_timestamp(builds.endtime)-unix_timestamp(builds.starttime))) as seconds from builds left join (slaves, builders) on (builds.slave_id=slaves.id and builds.builder_id=builders.id) where builds.starttime > '2014-01-01' and builds.starttime < '2014-01-31' and slaves.name like "bld-linux64-ec2%" group by builders.name order by builders.category, builders.name; 

I think the schema is this:
http://mxr.mozilla.org/build/source/buildbot/master/buildbot/db/schema/tables.sql

Comment 1

[Brandon Johnson [:cyborgshadow]]

Yes, they're both as optimal as they're going to be given the data. :)

I would however recommend using BETWEEN instead of starttime > and starttime <
It's just syntactically cleaner, no real performance boost.

An example is your 2nd query:

select builders.name, round(avg(unix_timestamp(builds.endtime)-unix_timestamp(builds.starttime))) as seconds from builds left join (slaves, builders) on (builds.slave_id=slaves.id and builds.builder_id=builders.id) where builds.starttime between '2014-01-01' and '2014-01-31' and slaves.name like "bld-linux64-ec2%" group by builders.name order by builders.category, builders.name;

Comment 2

[Armen [:armenzg]]

Thanks for the cleaner syntax!

I will open other bugs if I have newer queries.