News for hackers. Stuff that matters.
Re:How I slashed a SQL query's runtime with two Unix commands by jerry40 on Tuesday August 07, 2018 @05:20AM (#17704943)
As far as I understand, 'project_commits' and 'commits' have one-by-one relationship (project_commits.commit_id = commits.id). From my point of view it is a strange design since they could just add 'project_id' column into 'commits' table. 'project_commits' table seems to be redundant here.

I'd write this query:

    select pc.project_id, date_format(c.created_at, '%x%v1'), count(*)
    from commits c
        join project_commits pc 
            on c.id = pc.commit_id
    group by pc.project_id, date_format(c.created_at, '%x%v1')
And I'd use left join only at the stage when it is needed to join 'projects' dictionary with the result of the query.

There are no comments on this comment yet.