How to convert 3 selects into one query

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

can someone help me turn this 3 querys into one?

I gather that it would be faster and better than having the 3 select running all the time.

this runs in python with flask and the db is mysql

mycursor.execute('SELECT COUNT(*) FROM testes__db WHERE module = %s', (line[0],))


mycursor.execute('SELECT COUNT(*) FROM result_testes__db a1 INNER JOIN testes__db a2 ON a1.req_id = a2.id and a2.module =%s  and a1.test_id =%s',(int(line[0]),int(session['test_id']),))


mycursor.execute('SELECT COUNT(*) FROM result_testes__db a1 INNER JOIN testes__db a2 ON a1.req_id = a2.id and a2.module =%s and a1.test_id =%s where eval=0',(int(line[0]), int(session['test_id']),))

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

can someone help me turn this 3 querys into one?

SELECT COUNT(*) FROM testes__db WHERE module = %s
UNION ALL
SELECT COUNT(*) FROM result_testes__db a1 INNER JOIN testes__db a2 ON a1.req_id = a2.id and a2.module =%s and a1.test_id =%s
UNION ALL
SELECT COUNT(*) FROM result_testes__db a1 INNER JOIN testes__db a2 ON a1.req_id = a2.id and a2.module =%s and a1.test_id =%s where eval=0

Then add all parameters… You’ll save 2 database roundtrip times which is not much. If you want to overdo it, and "eval" is a bool, you could do:

SELECT 0, COUNT(*) FROM testes__db WHERE module = %s
UNION ALL
SELECT eval, COUNT(*) FROM result_testes__db a1 INNER JOIN testes__db a2 ON a1.req_id = a2.id and a2.module =%s and a1.test_id =%s GROUP BY eval

So you get the count for both values of "eval" in one query.

I gather that it would be faster and better than having the 3 select running all the time.

That’s not necessarily the case.

First, you should benchmark it. Back when I was doing websites, I added a query log at the bottom of the page showing all queries and their execution time. This was displayed only to a user logged in with admin credentials, of course, otherwise it would be a rather large security hole. This is a nice way to immediately spot slow queries while browsing the site.

You can do this with a wrapper object on the database connection.

Then once you know which queries are actually a problem, you can spend time optimizing them, or doing other things like caching the results.

Method 2

Plan A:

SELECT  ( SELECT COUNT(*) ... ) AS count1,
        ( SELECT COUNT(*) ... ) AS count2,
        ( SELECT COUNT(*) ... ) AS count3 ;

Plan B:

SELECT 'count1' AS Name, COUNT(*) ... 
UNION ALL
SELECT 'count2' AS Name, COUNT(*) ... 
UNION ALL
SELECT 'count3' AS Name, COUNT(*) ... 

(By including Name, you have a clue of which count is which.)

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