Multiple small queries vs a single long query. Which one is more efficient?

Question

Which one the below approaches is more efficient:

Single longer query:

"SELECT COUNT( * ) AS num
FROM (

    SELECT users.UID, cnumber
    FROM users
    JOIN identity ON users.UID = identity.UID
    WHERE  'abc'
    IN (google, facebook, twitter, linkedin)

) AS userfound
JOIN jobs ON userfound.cnumber = jobs.cnumber
WHERE JID = 24";


if(resultfromquery == 1)
    //Some code here
else
    //Some code here

Break the longer query into multiple single table queries:

uid = "SELECT UID FROM identity WHERE 'abc' IN (google, facebook, twitter, linkedin)";
cnumber_from_usertable = "SELECT cnumber FROM users WHERE UID = 'uid'";
cnumber_from_jobtable = "SELECT cnumber FROM jobs WHERE JID = 24";

if(cnumber_from_usertable == cnumber_from_jobtable)
    //Some code here
else
    //Some code here

There is a noticeable overhead to just perform a query. Hence doing small queries has that overhead multiple times. Unless your large query is pretty inefficient then it will be more efficient than several small queries. With your example here the difference is probably tiny, but where it becomes very noticeable is when you loop around the results of one query and perform another query for each result. — Kickstart, Apr 08 '13 at 09:20
Yes, exactly. The DB is practically empty. There's hardly any difference between the two, currently. So, I'm in 2 minds about which one to go with. — om_deshpande, Apr 08 '13 at 09:28
The most important resource is always the user. If a big query makes the user wait and small ones let the user start working while the data is still loading I would say, go for the last. — Aridane Álamo, Jun 13 '17 at 14:06

score 8 · Accepted Answer · answered Apr 08 '13 at 09:04

measure the microtime difference ;-) I would go with the single query though, less connections, less wait time and it's designed to handle those kind of things.

http://www.phpjabbers.com/measuring-php-page-load-time-php17.html

<?php
$time = microtime();
$time = explode(' ', $time);
$time = $time[1] + $time[0];
$start = $time;
?>
"SELECT COUNT( * ) AS
FROM (

    SELECT users.UID, cnumber
    FROM users
    JOIN identity ON users.UID = identity.UID
    WHERE  'abc'
    IN (google, facebook, twitter, linkedin)

) AS userfound
JOIN jobs ON userfound.cnumber = jobs.cnumber
WHERE JID = 24";


if(resultfromquery == 1)
    //Some code here
else
    //Some code here

<?php
$time = microtime();
$time = explode(' ', $time);
$time = $time[1] + $time[0];
$finish = $time;
$total_time = round(($finish - $start), 4);
echo 'Page generated in '.$total_time.' seconds.';
?>

score 5 · Answer 2 · answered Apr 08 '13 at 09:57

Actually I have somewhat different view on this. MYSQL support subqueries and one of the reasons being there is breaking the larger query into smaller. If you are using INNODB engine and are using the parameters correctly for example tmp table, heap table, bufferpool, or query cache then you may get better results then smaller queries. MYSQL was designed to execute alot of small queries so it would be better to have more small queries then a larger query.

If a larger query was a better option then we did not had the need to create tool like Infinidb and INfobright that uses column based structure to run large queries, where as MYSQL is row based.

So think about it, small queries would result better then a large single query and overhead is not of queries it is of connections and again MYSQL is very cheap with connections so no worries their either. BUt if your MYSQL is creating threads then you have nothing to worry about.

In short small few queries vs giant large query would be my choice.

That's an interesting POV. Could you please elaborate "MYSQL was designed to execute alot of small queries"? — om_deshpande, Apr 08 '13 at 12:42
Indeed many people do not realise this, but if you use your development skills wisely and store results in tmp tables efficiently small queries will do the magic. And as I said MYSQL is row based, for larger sets you need to move into columnar databases. With MYSQL you would need to do table partitioning eventually to get faster results, which again tells us that MYSQL like smaller queries. — Masood Alam, Apr 08 '13 at 16:15

Multiple small queries vs a single long query. Which one is more efficient?

2 Answers2

Linked