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.
CodePudding user response:
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
.