How I slashed a SQL query's runtime with two Unix commands
1–10 of 91 posts
Re: How I slashed a SQL query's runtime with two Unix commands
#2Try a real analytical database such as Vertica
Re: How I slashed a SQL query's runtime with two Unix commands
#3Try a real analytical database such as Vertica
What is the definition of a real analytical database?
Re: How I slashed a SQL query's runtime with two Unix commands
#4The question im interested in is why MySQL did not use the index for the lookup.
Re: How I slashed a SQL query's runtime with two Unix commands
#5The 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.
Re: How I slashed a SQL query's runtime with two Unix commands
#6If you initiate the shell commands from inside the database, does it count as less hacky? :) https://github.com/petere/plsh
Re: How I slashed a SQL query's runtime with two Unix commands
#7Re: How I slashed a SQL query's runtime with two Unix commands
#8The 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.
Re: How I slashed a SQL query's runtime with two Unix commands
#9[deleted]
Re: How I slashed a SQL query's runtime with two Unix commands
#10The 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.
It can be very frustrating that, even with updated statistics, the optimizer decides to go a different way with a query. While using Sybase, this happened quite a lot when tables got rather large (corporation large not Google large). The normal response is to force the indexes on the query. Due to log space issues, I do remember having an awk script between queries back in the Ingres days.