Performance issues using temporary tables

All we need is an easy explanation of the problem, so here it is.

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

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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

Method 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.

Method 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 .

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply