how to execute multiple mysql queries fast

Question

I am using multiple MySQL select queries for getting the result. The output of the first query is Input of the second query, like this, I am using fifteen(15 queries at the time). Because I want to get a result till the 15th stage.

When I execute all these queries it takes too much time to get the output, for ex- it takes more than 15 to 20 min to produce the result.

Basically, there are 15 levels in my code and I am calculating the total amount for each level and that's why I am using 1 query for each level. So the total level is 15 and my MySql query is 15.

I have a DB table where 4 columns are there-

ID, MemberID, SponseredID, Amount.

I prepared the query to calculate the SUM of Amount of each Level.

you can check the code below:

SELECT SUM(amt) as level1 
FROM adm 
WHERE sid='MT00001';

SELECT SUM(amt) as level2 
FROM adm 
WHERE  sid IN (SELECT mid 
               FROM adm 
               WHERE sid='MT00001');

SELECT SUM(amt) as level3 
FROM adm 
WHERE sid IN (SELECT mid 
              FROM adm 
              WHERE sid IN (SELECT mid 
                            FROM adm 
                            WHERE sid='MT00001'));

SELECT SUM(amt) as level4 
FROM adm 
WHERE sid IN (SELECT mid 
              FROM adm 
              WHERE sid IN (SELECT mid 
                            FROM adm 
                            WHERE sid IN (SELECT mid 
                                          FROM adm
                                          WHERE sid='MT00001')))

This is till level 4 and here also these 4 queries take too much time to execute.

I am getting the result but it takes too much time, approx 15 min to execute all 15 queries.

@James- Mine question is similar to below link but I didn't get expected result. https://stackoverflow.com/questions/41913460/mysql-recursive-get-all-child-from-parent — manish sharma, Jul 22 '19 at 05:07

score 0 · Answer 1 · answered Jul 22 '19 at 05:24

0

Don't use IN ( SELECT ... ); it used to perform very poorly.

Instead, use JOIN or EXISTS(), depending on the details of what you are doing.

answered Jul 22 '19 at 05:24

Rick James

135,179
13
127
222

score 0 · Answer 2 · answered Jul 22 '19 at 06:43

Instead of making subquery for each table, try using join.

For your question use group by clause for each group or category. Search for recursive query.

You may use this for your reference https://learn.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql?view=sql-server-2017

how to execute multiple mysql queries fast

2 Answers2