Alternative to subselect

[sidsel]

My query with a subselect takes about 10 times longer to execute than two seperate queries.

According to phpmyadmin 0.1044 seconds:
SELECT photos.header, (SELECT COUNT(id) FROM comments AS b WHERE b.post_id = photos.post_id) FROM photos WHERE post_id = 103877;

According to phpmyadmin 0.0132 seconds:
SELECT header FROM photos WHERE post_id = 103877;
SELECT COUNT(id) FROM comments WHERE post_id = 103877;

I like the "beauty" of one combined query but not at the cost of 10 times longer execution time. Are there any ways to optimize?

[Piranha]

Indeed there is a way to optimize. The reason it takes way longer is because you join the two tables "WHERE b.post_id = photos.post_id". Instead try to execute the queries without joining them. I think that the database is "smart" enough to transform it to this, but it is worth a try anyway.

SELECT photos.header, (SELECT COUNT(id) FROM comments AS b WHERE b.post_id = 103877)  FROM  photos WHERE post_id = 103877;

That is one part of it. I don't know for sure if it will help or not.

Another part is that you probably tried the first way and then the second way. After it have already been selected it stays in the memory for some time, and are way quicker to get again. Try to execute the queries in another order and see what will happen. Also execute them lots of times and take a mean or median of that to get a more reliable result.

A third part is to make sure that the comments.post_id is indexed. If it is then there is no need to retreive everything from the table, otherwise it will go through everything and of course that takes time.

[kakki]

what about:

SELECT MIN(photos.header), COUNT(comments.id) 
FROM comments,photos 
WHERE comments.post_id = photos.post_id 
      AND post_id = 103877;
GROUP BY comments.post_id