mysql statement with nested SELECT - how to improve performance

Question

This statement appears inefficient because only one one out of 10 records are selected and only 1 of 100 entries contain comments.

What can I do to improve it ?

$query = "SELECT 
A,B,C,  
(SELECT COUNT(*)
    FROM comments
    WHERE comments.nid = header_file.nid) 
    as my_comment_count
FROM  header_file
Where A = 'admin' "

edit: I want header records even if no comments are found.

Answer 1

You can try to use a Left Join, this could allow better optimization:

Select a, b, c, Count(*) As my_comment_count
From header_file h
Left Outer Join comments c On ( c.nid = h.nid )
Group By a, b, c
Where A = 'admin'

Answer 2

You can add index on a A and nid column.

Answer 3

I am using an inner join here because it sounds like you only want header_file records that contain comments. If this is not the case, change it to a left outer join:

select h.a, h.b, h.c, c.Count
from header_file h
inner join (
    select nid, count(*) as Count
    from comments
    group by nid
) c on c.nid = h.nid
where h.a = 'admin'

Answer 4

Insert the comment count number directly in the table, count(*) isn't very efficient.

http://www.mysqlperformanceblog.com/2006/12/01/count-for-innodb-tables/

Answer 5

From question:

This statement appears inefficient ...

How do you know it is inefficient?
Do you have execution plan?
Did you measure execution times?
Are you sure it uses index?
You commented Peter Lang's answer: ... not sure if any performance gain here - is based on what?

Basic thing you should know about query execution:

• Most modern RDBMS-s have query optimizer that analyzes your SQL and determines optimal execution plan.

Your feeling that some query is "bad" doesn't mean anything. You need to check execution plan and then you see if there is anything you can do to improve performance.

For MySql, see article: 7.2.1. Optimizing Queries with EXPLAIN

You could also run SQL from answers and compare execution plans to see if any of proposed solution gives better performance.