In another use case, I will need to run that query for a specific date range only. For best performance, should I make a temporary table filtering
estado_clienteby date, and then use that temp table in the query?
First, and most important: Your rolling sum will be based on the rows used in the query only. You may already have realized that, but just in case, I thought I should cover it.
If you processed the sample data from the question, but only included rows dated September 1-25, 2017, you won't get:
idcliente nombre apellido fecha haber debe SUM 6 Matias Blanco 2017-09-24 4312 0 -18788 6 Matias Blanco 2017-09-25 0 10000 -28788
Instead, you'll get:
idcliente nombre apellido fecha haber debe SUM 6 Matias Blanco 2017-09-24 4312 0 4312 6 Matias Blanco 2017-09-25 0 10000 -5688
That's because the rows from July are excluded, so their
debe are not calculated into the
To get the first set of results (with an absolute
SUM, where you get the same value for each row even when you exclude older rows from your output), you have to include all records prior to your date range in the full process, and filter out the ones from before the target dates in the final query.
If the second set of results are what you're after (where the
SUM is relative to the set of dates being displayed), then you can fully filter out your full date range before calculating the
In either case, the upper bound of a date range should be applied before calculating the
SUM - records past the dates you want have no impact on the
SUM for earlier dates, regardless.
This sqlfiddle link shows the same three queries as the previous link, plus versions of the self-join and variable queries that restrict the output to rows from September 1-25, 2017 and still show the "absolute" sums. I've added an initial column to the results of each, to make it clear which query is which.
So, there are a few ways to do the filtering before processing. Let's just consider two: simply filter out the rows by adding WHERE clauses to the existing queries, or write the
estado_cliente data for the only the rows we need to process to a temporary table. The 4th and 5th queries in the link just above are using
Ultimately, I can only recommend that you try both, and see which performs better for you.
I can provide some guidance as to what I would expect:
Writing to disk is generally the most time-consuming operation you can perform in a database (reads from disk are a close second, normally - solid state drives tend to reduce the cost of both of these operations). So, writing the data you need to a temporary table can actually make a query take longer; if the database engine was able to process everything in memory, and didn't need to write any intermediate data to disk, then I would expect writing to the temp table would be worse.
If you would be using the temporary table for other queries as well, then creating it could result in a net improvement overall. If there are a large number of
estado_clientecolumns you won't be using, the temporary table would fit more data into each page, and you'd have fewer pages to bring into memory. If
estado_clientedoesn't have many columns beyond the ones you're using, then there is less benefit. The fact that every page has only the rows you want to work with might still mean fewer page to bring into memory. Note that, in the version of the query with the self-join, you are accessing
estado_clientetwice, so a temporary table might benefit that version of the query as well.
I don't use MySQL heavily, but in MS SQL Server (where I do most of my work), I have sometimes seen queries that completed faster broken into two queries (one that populates a temp table, and one that performs the final steps using the temp table) than as a single query. I believe this is simply a matter of the complexity of the query making it difficult for the database engine to find an optimal execution plan within the time limits it has for that step of things. It does find optimal plans for the two separate queries, so they go fast; it picks the best plan it can for the more complex query in the time allotted, and that plan can turn out to be bad.
My "gut instinct" says that the query that uses variables is pretty unlikely to benefit from the temp table (say, 25% chance), and the query using a self-join is somewhat unlikely (say, 45% chance).
But like I said earlier - It's probably worth the time to try both options (filter the unwanted date rows in the query, and write the desired date rows to a temporary table), and see which way seems better with your data and system.