Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

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

spinellis.gr

Post reply on HN
Print view View original

41–50 of 91 posts

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

#41

Post by wild_preference »

pjmlp wrote: post #37
languagehacker wrote: post #30

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

Lots of "modern" blog posts seem like people rediscovering that X exists. A consequence of many skipping on traditional learning processes and how developers are now also forced into castings and portfolios.

There is no traditional process that has you knowing everything about everything much less be able to reverse identify an implementation with the underpinning concept.

There are things I know much more of than you do. Seems a bit masturbatory for me to see you miss a concept and go “ah, the modern lack of traditional learning processes!”

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

#42

Post by barrkel »

thecopy wrote: post #4

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

Since there's nothing limiting the set of rows from project_commits, it might as well table-scan it. The primary key on commits will be used for each left join lookup.

Nested loops aren't that different, performance-wise, than sorted merge joins. Sorting takes O(n log n); whereas the nested loop does n lookups, each taking O(log n), for a similar O(n log n). Memory allowing, a hash join has more potential for speedup.

There should be a locality win from a sorted merge - depending on the correlation between scan order and foreign key order, the index lookups in the nested loop may be all over the place. Usually this doesn't matter much because you don't normally do 5+ billion row joins.

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

#43

Post by [deleted] »

barrkel wrote: post #40
pytyper2 wrote: post #38

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's no predicate or limit or inner join that could reduce the set of rows, so the table might as well be scanned.

[deleted]

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

#44

Post by protomyth »

Sean1708 wrote: post #39
protomyth wrote: post #35

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

I think they just wanted all of the data.

Is there an actual schema and does MariaDB do index scans?

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

#47

Post by sidlls »

languagehacker wrote: post #30

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

Well it's hardly surprising given the emphasis on CS trivia and fad-devops that exists in this industry. Basics like this are often neglected because employees are too busy chasing Kafka and Kubernetes or re-implementing skip lists on whiteboards. After all, if you can recall your textbook CS surely you can just re-invent the thousands of PhD and engineering hours that went into this stuff previously on an ad hoc basis for our projects and scale it with K8s.

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

#49

Post by Spooky23 »

pjmlp wrote: post #37
languagehacker wrote: post #30

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

Lots of "modern" blog posts seem like people rediscovering that X exists. A consequence of many skipping on traditional learning processes and how developers are now also forced into castings and portfolios.

It's also a negative side-effect of the improvement of software and automation in this space.

In ye olden times, there would be an grumpy DBA in this story terminating the query and yelling at the user. Many, many people are consuming databases without understanding how to approach SQL optimization, because nobody is forcing them to!

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

#50

Post by sidlls »

Spooky23 wrote: post #49
pjmlp wrote: post #37

Earlier quoted context omitted.

Lots of "modern" blog posts seem like people rediscovering that X exists. A consequence of many skipping on traditional learning processes and how developers are now also forced into castings and portfolios.

It's also a negative side-effect of the improvement of software and automation in this space. In ye olden times, there would be an grumpy DBA in this story terminating the query and yelling at the user. Many, many people are consuming databases without understanding how to approach SQL optimization, because nobody is forcing them to!

There's a happy middle ground between what we have now and those DBAs playing hardass without even trying to understand the need.
Post reply on HN