What are the advantages and disadvantages of using COUNT() in a subquery to retrieve the number of related records in PHP?

When using COUNT() in a subquery to retrieve the number of related records in PHP, the advantage is that it allows for more complex queries and can provide accurate counts. However, the disadvantage is that it can impact performance, especially when dealing with large datasets. 

$query = "SELECT *,
    (SELECT COUNT(*) FROM related_table WHERE related_table.id = main_table.id) AS related_count
    FROM main_table";
$result = mysqli_query($connection, $query);

if(mysqli_num_rows($result) > 0) {
    while($row = mysqli_fetch_assoc($result)) {
        echo "Related count: " . $row['related_count'] . "<br>";
    }
}

Keywords

COUNT() subquery PHP advantages disadvantages

Related Questions