将2个查询合并为1个

Question

I'm looking to optimize two queries into one, if possible.

My first query searches for all the authors of a lyrics... Then, for each author found, i want to find the total numbers of lyrics the author was involded in...

Right now, im executing the first query and for each row found, i'm launching another query to get the authors total lyrics he was involved... So, if there is 4 authors i will end up launching 4 more queries... That is to many queries in my opinion. That is why i've decided to write here, so i can get help on how to optimize my query...

This is the query i'm executing to get the author(s) responsable for a lyrics:

$sql = "SELECT author.author_id, author.name
    FROM track INNER JOIN lyrics_author ON track.lyrics_id = lyrics_author.lyrics_id
    INNER JOIN author ON lyrics_author.author_id = author.author_id
    WHERE track.track_id = $trackid ";

This is the query to get the total number of lyrics the author as writing:

$total = "SELECT lyrics_author.author_id, count(*) as total
    FROM lyrics_author
    WHERE lyrics_author.author_id = $author_id
    GROUP BY lyrics_author.author_id";

This is a sample of the code:

<?php
$trackid = 5;

$sql = "SELECT author.author_id, author.name
    FROM track INNER JOIN lyrics_author ON track.lyrics_id = lyrics_author.lyrics_id
    INNER JOIN author ON lyrics_author.author_id = author.author_id
    WHERE track.track_id = $trackid ";

$result_author = @ $conn->query($sql);

while ($row_author = $result_author->fetch_assoc()) {
    $author_id = $row_author['author_id'];

    $total = "SELECT lyrics_author.author_id, count(*) as total
        FROM lyrics_author
        WHERE lyrics_author.author_id = $author_id
        GROUP BY lyrics_author.author_id";

    $result_total_lyrics = @ $conn->query($total);
    $t = $result_total_lyrics->fetch_assoc();

    echo $t['total'];

    $result_total_lyrics->free();
}

$result_author->free();
?>

Is it posible to optimize this query? If yes, how? Is there a link you could refer, so i can learn...

Thanks Marco

Answer 1

SELECT
  author.author_id,
  author.name,
  COUNT(DISTINCT more_tracks.lyrics_id) AS total
FROM track
INNER JOIN lyrics_author USING (lyrics_id)
INNER JOIN author USING (author_id)
LEFT JOIN lyrics_author AS more_tracks USING (author_id)
WHERE track.track_id = $trackid
GROUP BY author.author_id