Are these SQL statements optimized?

RESOLVED FIXED

Status

RESOLVED FIXED
5 years ago
4 years ago

People

(Reporter: armenzg, Assigned: bjohnson)

Tracking

Details

(Reporter)

Description

5 years ago
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
(Assignee)

Comment 1

5 years ago
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;
Assignee: server-ops-database → bjohnson
(Reporter)

Comment 2

5 years ago
Thanks for the cleaner syntax!

I will open other bugs if I have newer queries.
Status: NEW → RESOLVED
Last Resolved: 5 years ago
Resolution: --- → FIXED
Product: mozilla.org → Data & BI Services Team
You need to log in before you can comment on or make changes to this bug.