I know there’s many questions/answers for slow queries, but I’m struggling to relate an existing answer to my example.
I have the following simple query which counts article views in a subquery:
SELECT articles.id, articles.views, articles.title, articles.slug, articles.created_at, (SELECT count(*) FROM tracking WHERE element_id = articles.id AND tracking_type = 'article_view') AS tracking_views FROM articles WHERE articles.company_id = 123 ORDER BY articles.created_at DESC
This particular company has ~250 articles, and the query takes over 12 seconds.
Is there a better/more efficient way I could be doing this?
Try joining to a group by. Its pretty hard to say without knowing how many articles / views and companies there are though.
What you want is for SQL to be able to to the aggregation of
tracking in one go, rather than individually for every row in the result, which is implied by the position of your
tracking_view sub select.
If your lucky (I didnt check) the join to the
counts sub select will be smart enough to skip any articles that are not for the right company. If not you can include the join back to company in the
counts sub select.
select a.*, counts.count from articles a join ( select count(*) as count, element_id from tracking where tracking_type = 'article_view' group by tracking.element_id ) as counts on counts.element_id = a.id where a.company_id = 123 ORDER BY articles.created_at DESC