Performance issues using temporary tables

Question

I have a program which takes a few hours to run and does lots of different queries along the way. Individual queries take 3-4 minutes, but I need to run a large number of these sequentially in order to obtain the final result.

I have realised that there is a serious bug because the database is changing in real time, and so the results were inconsident, so I need all the queries to run against the same "snapshot" of the database otherwise, if it updates during the run, the results can be inconsistent.

So to solve this, I created some temporary tables at the start of the script, which are basically just SELECT * FROM table, and the queries then run against those instead of the live tables themselves, and I have verified that the results are correct/consistent.

Howeverr, the problem is that with temporary tables, the whole program takes 40-45 hours to run, as opposed to 2-3 hours without temporary tables (which I am hoping to reduce). When I look at the performance of the instance that I run the queries on (using MySQL Workbench) the CPU and memory usage are very low; yet when I don't use temporary tables, the CPU usage on my local instance flatlines at 100%.

So my theory is that, since temporary tables are stored in RAM on the server, the server does not have sufficient RAM, and the bottleneck is there. Unfortunately I don't have any access to the server so I can't verify this. So what I am hoping for from this question is to get some feedback as to whether this problem is likely caused by insufficient resources on the server, or perhaps something else.

Edit: I have also noticed in MySQL Workbench, that the individual queries are taking 15 times longer to run on the server against the temporary tables, than when it was running against the raw tables

• nbk: Have your temporary tables the same indexes as the original tables?
• danblack: Does running in a repeatable read transaction help generate consistent results? details would help.
• Rick James: Temporary tables are stored either in RAM or on disk. If they need to be on disk (too big for ram, contain TEXT, etc), then, yes, it is slower.
• Rick James: 45 hours vs 3 hours -- something is being done "wrong".

Answer 1

Create indexes on temporary tables. Syntax is same as for regular tables.

Answer 2

Do you have something like a "data warehouse"? And you are doing "reports? That is, if you are summarizing (COUNT, SUM, etc) the information, then build and maintain "summary table(s)". More discussion: http://mysql.rjweb.org/doc.php/summarytables

That will be much faster and can be as up-to-date as necessary. Please provide more details; I should be able to help you further.

Answer 3

in temp table you can make some index field , for example other table`s name , and make a regular index with integer like id . its best way to speedup your queries .

• Hannah Vernon: You should expand this answer with some code; for an example of what I mean, look at minimal reproducible example.