Order by select count(*) and LIMIT is very slow

Question

I have this query in my program, when I do some sorting with select count(*) field from the query, I dont know why, it very slow when running that query.

The problem is when i do some ordering from posts_count, it run more slower than i do ordering with the other field.

Here's the query: select 'tags'.*, (select count(*) from 'posts' inner join 'post_tag' on 'posts'.'id' = 'post_tag'.'post_id' where 'tags'.'id' = 'post_tag'.'tag_id') as 'posts_count' from 'tags' order by 'posts_count' asc limit 15 offset 0;

Here's the execution time :

Please someone help me to improve this query , Thank you.

What i expect is the query can be run faster.

Answer 1

SELECT t.*, COUNT(*) AS count
FROM tags AS t
LEFT OUTER JOIN post_tag AS pt ON t.id = pt.tag_id
GROUP BY t.id
ORDER BY count ASC LIMIT 15 OFFSET 0;

You should make sure post_tag has an index starting with the tag_id column. You didn't include your table definition in your question, so I must assume the index is there. If the primary key starts with tag_id, that's okay too.

You don't need to join to posts, if I can assume that a row exists in post_tag means it must reference an existing row in posts. You can get the information you need only by joining to post_tag.