31–40 of 91 posts
In this case it would be much faster to have an intermediate table or column with that statistic that could then be properly indexed.
Earlier quoted context omitted.
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
I don't know mysql/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]. In this case it would be much faster to have an intermediate table or column with that statistic that could then be properly indexed.
https://www.definitions.net/definition/sargable
https://stackoverflow.com/questions/799584/what-makes-a-sql-...
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…
I highly recommend Clickhouse for this. It is blazing fast and can do over 100GB/s on a single modern machine if you have enough RAM. And it is very easy to install and configure.
Fast as hell and surprisingly performance in low memory conditions.
Try a real analytical database such as Vertica
What is the definition of a real analytical database?
Am I missing something or is this blog post just someone discovering that ETL exists
A consequence of many skipping on traditional learning processes and how developers are now also forced into castings and portfolios.
Am I missing something fundamental like a WHERE clause? I see a join, but no limiting of rows beyond that.
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.