How I slashed a SQL query's runtime with two Unix commands

The distinct on the date format is the problem, probably should use a separate query on each table to fill in the date output as a new column first, and then join afterwards.<p>Also wrong tool for the job comes to mind. MariaDB&#x2F;MySQL are just not great at joins and query planning, and at this scale, especially with all the work involved to export and use unix tools, why not use any of the columnar data warehouses that can handle this much faster?<p>Export it to bigquery with CSVs and the query will probably finish in 30 seconds.

I don&#x27;t know mysql&#x2F;mariaDB that well, but I know in ms sql server that doing a DISTINCT on a Function([Column]) would not use an index on [Column].<p>In this case it would be much faster to have an intermediate table or column with that statistic that could then be properly indexed.

I&#x27;d like to see the runtime for the same query but (1) without &#x27;distinct&#x27;, (2) without the date formatting and (3) with an inner join.

Am I missing something or is this blog post just someone discovering that ETL exists

If you initiate the shell commands from inside the database, does it count as less hacky? :) <a href="https:&#x2F;&#x2F;github.com&#x2F;petere&#x2F;plsh" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;petere&#x2F;plsh</a>

I&#x27;ve run into situations like this where I was able to use a window function or a materialized view, to get huge speed gains. It basically was restructuring the tables into a new view, much like the shell script did in this case, and that view then could be optimized by the query planner much more effectively.<p>One was with an internal database we used for tracking employee time and generating invoices and reports. I forget the exact details but this technique took it down from an hour to a second, or similar.<p>Another time was more recently in an interview, using the mysql employee sample database. I had a naive query that did most of what was needed, but after quite a bit more work I was able to make a materialized view that caused the query to go from using 64+GB of RAM and dying from OOM after 4 hours, to running in 45 minutes in 2GB of RAM.<p>It usually takes me hours and hours to put together though. Because I do it so infrequently.

Isn&#x27;t the Unix join essentially doing an inner join? Why is the original SQL doing a left join while the text processing with Unix join is doing an inner join?<p>Yes, the left join will scan the whole table. Left join means to return all the rows from the left hand side table. It won&#x27;t use the index.<p>Inner join with index is way faster than left join.

If you find you&#x27;re exporting and running via `uniq` or the like, `create temporary table as` is probably what you want. If you think about it, it&#x27;s essentially what you&#x27;re doing, without the additional filesystem involvement.<p>The optimizer <i>should</i> be handling this for you, though for very large datasets, constructing a new table from a query essentially avoids the locking issues you might otherwise run into.

I&#x27;d also like to see a breakdown of the time taken to complete the project.<p>RAM is cheap and jumping from 16GB to 64GB (or even 128GB) might have cost as much as the analysis time.<p>The only clue to that a memory upgrade might have been a quicker fix was that the merged file was 133G. Seems to me that an upgrade to 128GB of RAM might have led to a dramatically shorter query execution time.

I think the date_format call forces a table scan, the query could be rewritten to be much faster. Optimizing the sql statement seems less error prone than extracting the data to text files.

There is some more information in this reddit thread<p><a href="https:&#x2F;&#x2F;www.reddit.com&#x2F;r&#x2F;programming&#x2F;comments&#x2F;94r6w6&#x2F;how_i_slashed_a_sql_query_runtime_from_380_hours&#x2F;" rel="nofollow">https:&#x2F;&#x2F;www.reddit.com&#x2F;r&#x2F;programming&#x2F;comments&#x2F;94r6w6&#x2F;how_i_s...</a>

As far as I understand, &#x27;project_commits&#x27; and &#x27;commits&#x27; 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 &#x27;project_id&#x27; column into &#x27;commits&#x27; table. &#x27;project_commits&#x27; table seems to be redundant here.<p>I&#x27;d write this query:<p><pre><code> select pc.project_id, date_format(c.created_at, &#x27;%x%v1&#x27;), count(*) from commits c join project_commits pc on c.id = pc.commit_id group by pc.project_id, date_format(c.created_at, &#x27;%x%v1&#x27;) </code></pre> And I&#x27;d use left join only at the stage when it is needed to join &#x27;projects&#x27; dictionary with the result of the query.

Am I missing something fundamental like a WHERE clause? I see a join, but no limiting of rows beyond that.

MariaDB can do sort&#x2F;merge joins. It&#x27;s rare that you want to force one, but you can. That&#x27;s what IGNORE INDEX is for.<p>If you&#x27;re testing, take a sample of the database and test on that.<p>First try the simple form:<p><pre><code> explain select project_commits.project_id, date_format(commits.created_at, &#x27;%x%v1&#x27;) as week_commit from commits, project_commits where project_commits.commit_id = commits.id; </code></pre> That ought to call for a sort and merge.

The query is simply very badly written. Who makes a left join of a 5b rows table to a smaller table and at the same time expects a distinct from the “left” table??? If the distinct is important to solve the problem without expecting the join to return “true” then why have a join at first place???

I once had a huge performance problem for a simple self-join that I expected would run quickly.<p>I talked to our resident SQL expert (retired from IBM, now doing a job as the lab administrative assistant). She suggested sorting the data before inserting it.<p>That sped up the query.<p>it still bother me that data order matters so much. But, I can&#x27;t reproduce the problem in MySQL any more anyway.

After reading the post, and considering the time of export&#x2F;import the data, I think will be better to denormalize the data (ie:precalculate the result).<p>I have something like in the near past, but with much heavy calculations. Denormalizing using triggers turn query that take minutes in less 1 second.

How long does it take to run the distinct on its own? I&#x27;d be interested to know what happens if the distinct is in a sub query then the result is joined with the commits table<p>Ensure project_commits.project_id is in an index before running the test

The question im interested in is why MySQL did not use the index for the lookup.

Naive question: are your tables partitioned?

TLDR: author doesn’t know how to use database indexes properly.