CODEWITHSUNDEEP
sqlmysqlperformance
ernie
ernie

Reputation: 119

mysql statement with nested SELECT - how to improve performance

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.

Upvotes: 0

Views: 1695

Answers (5)

zendar
zendar

Reputation: 13562

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.

Upvotes: 1

Telos
Telos

Reputation: 1

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/

Upvotes: 0

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171391

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'

Upvotes: 1

Svisstack
Svisstack

Reputation: 16616

You can add index on a A and nid column.

Upvotes: 1

Peter Lang
Peter Lang

Reputation: 55524

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'

Upvotes: 0

Related Questions