51–60 of 91 posts
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.
Am I missing something fundamental like a WHERE clause? I see a join, but no limiting of rows beyond that.
where tableB.someCol != null
or something. Otherwise, the join is useless (or he should use an inner join)
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.
Yes, having tables with the size of 5B rows on a machine with 16GB RAM is just stupid, going to the disk will most probably always happen.
Am I missing something or is this blog post just someone discovering that ETL exists
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.
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…
TLDR: author doesn’t know how to use database indexes properly.
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.
That sped up the query.
it still bother me that data order matters so much. But, I can't reproduce the problem in MySQL any more anyway.
Earlier quoted context omitted.
What is the definition of a real analytical database?
by analytical database I think the person meant a columnar database. for mysql compatible examples that means infobright or infinidb.