0

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

4
  • 2
    Have your temporary tables the same indexes as the original tables?
    – nbk
    Jul 16 at 5:58
  • 1
    Does running in a repeatable read transaction help generate consistent results? details would help.
    – danblack
    Jul 16 at 7:18
  • 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
    Jul 16 at 18:50
  • 45 hours vs 3 hours -- something is being done "wrong".
    – Rick James
    Jul 16 at 18:50
1

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

1

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.

-2

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 .

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.