How to improve slow MySQL subquery

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?

Answer

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.

eg

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