MySQL 여러 개의 작은 쿼리와 단일의 긴 쿼리. 어느 것이 더 효율적입니까?

아래 방법 중 어느 것이 더 효율적입니다.

더 긴 단일 쿼리 :

"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

긴 쿼리를 여러 단일 테이블 쿼리로 나눕니다.

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

 

해결 방법

 

마이크로 시차 측정 ;-) 그러나 단일 쿼리를 사용하면 연결이 줄어들고 대기 시간이 줄어들며 이러한 종류의 작업을 처리하도록 설계되었습니다.

<?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.';
?>

 

참조 페이지 https://stackoverflow.com/questions/15875167