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

11–20 of 91 posts

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

#11

Post by alexpotato »

jarym wrote: post #5
thecopy wrote: post #4

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

Not sure if MySQL is the same but Postgres won't use an index if the statistics suggest doing so will be slower than a full table scan - would expect MySQL to be similar. With 5bn+ rows and a memory constraint, the type of index begins to make a difference - e.g. in Postgres I would have tried using a bloom index.

I believe that is the case with the old default being if more than 30% of the table was being returned, don't use the index.

I think that is configurable however.

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

#13

Post by mnw21cam »

thecopy wrote: post #4

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

Reading the article, the database did use an index for the lookup. However, if that index lookup is hitting spinning rust, then it can take 10ms or so per entry, which adds up.

In contrast, if you have sorted/indexed both tables on the join key, then the database is able to do a merge join. This is effectively what the command line implementation did, and is much faster, assuming you can get hold of sorted data quickly to begin with. If the tables are unsorted, then the extra cost of sorting them first needs to be added.

Most databases will evaluate several methods of running the query. Something like Postgres EXPLAIN will provide details of the method being used.

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

#14

Post by mnw21cam »

mutatio wrote: post #8
thecopy wrote: post #4

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

The query includes `date_format(created_at, '%x%v1') as week_commit` - meaning it would need to be computed for every row.

That's only in the SELECT statement, which only needs to be performed after the join has been done.

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

#16

Post by ckastner »

lunchladydoris wrote: post #12

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

My thoughts exactly.

The post references a StackExchange question [1] which has a simple analysis, and four helpful suggestions (similar to yours), as to which the author states:

"I tried the first suggestion, but the results weren't promising. As experimenting with each suggestion could easily take at least half a day, I proceeded with a way I knew would work efficiently and reliably"

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

#17

Post by manigandham »

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.

Also wrong tool for the job comes to mind. MariaDB/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?

Export it to bigquery with CSVs and the query will probably finish in 30 seconds.

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

#18

Post by nextweek2 »

I'd also like to see a breakdown of the time taken to complete the project.

RAM is cheap and jumping from 16GB to 64GB (or even 128GB) might have cost as much as the analysis time.

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.

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

#19

Post by schrodinger »

ckastner wrote: post #16
lunchladydoris wrote: post #12

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

My thoughts exactly. The post references a StackExchange question [1] which has a simple analysis, and four helpful suggestions (similar to yours), as to which the author states: "I tried the first suggestion, but the results weren't promising. As experimenting with each suggestion could easily take at least half a day, I proceeded with a way I knew would work efficiently and reliably"

1: https://dba.stackexchange.com/questions/213983/can-i-speed-u...

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

#20

Post by qoi2ijds0 »

mutatio wrote: post #8
thecopy wrote: post #4

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

The query includes `date_format(created_at, '%x%v1') as week_commit` - meaning it would need to be computed for every row.

Only for every returned row. The projection does not affect the underlying result set, it merely "shapes" the results you have received. WHERE clauses are always run before SELECT
Post reply on HN