Search code examples
mysqlsqldatabase-performance

how can i increase the performance of SQL?(sub query is used)

SELECT contents.*, 

(SELECT COUNT(*)
FROM comments
WHERE comments.scrap_id = contents.org_scrap_id) AS comment_count

FROM contents
ORDER BY comment_count

this is my intention. but it makes very long waits.

how can i increase the performance of this query?

Solution

  • You can increase the performance by creating an index on comments(scrap_id):

    create index comments_scrap_id on comments(scrap_id);

    You could also try phrasing this as a join and group by:

    SELECT co.*, count(com.scrap_id) as comment_count
FROM contents co left outer join
     comments com
     on co.org_scrap_id = com.scrap_id
GROUP BY co.id
---------^ or whatever the appropriate `id` is for this table
ORDER BY comment_count;

    But the index is likely to give better performance.