0

My query looks like that

SELECT topics.name, topics.topic_id, topics.creator, 
( SELECT COUNT(*) FROM answers 
WHERE answers.topic_id = topics.topic_id) 
AS replies FROM topics 
ORDER BY topics.bumped DESC, topics.date DESC LIMIT 500

However it takes 0.6s, I'm guessing that's because of the subquery that counts answers. Any other way to achieve this effect with better performance?

1
topics: INDEX(bumped, date) -- in that order
answers: INDEX(topic_id)

Please provide SHOW CREATE TABLE if you need to discuss this further.

500 is a lot of results -- more than a self-respecting UI should return. What will you do with them?

Also Consider this approach:

SELECT  topics.name, topics.topic_id, topics.creator, replies.ct
    FROM  
    (
        SELECT  topic_id, count(1) AS ct
            FROM  answers 
            GROUP BY topic_id
    ) AS replies
    JOIN  topics  ON replies.topic_id = topics.topic_id
    ORDER BY  topics.bumped DESC, topics.date DESC
    LIMIT  500

It may run faster because of reaching into answers only once.

If, however, there could be zero replies in some cases, you need a LEFT JOIN:

SELECT  topics.name, topics.topic_id, topics.creator, replies.ct
    FROM  topics
    LEFT JOIN (
        SELECT  topic_id, count(1) AS ct
            FROM  answers 
            GROUP BY topic_id
              ) AS replies  ON replies.topic_id = topics.topic_id
    ORDER BY  topics.bumped DESC, topics.date DESC
    LIMIT  500

This does need a relatively new version of MySQL so that the derived table can automatically be given an index.

Which approach is fastest? I can't tell; try them all.

0

A small change can do little improvement in query, by count (1) instead count(*)

SELECT  topics.name, topics.topic_id, topics.creator, 
    (
        SELECT  count(1)
            FROM  answers
            WHERE  answers.topic_id = topics.topic_id
    ) AS replies
    FROM  topics
    ORDER BY  topics.bumped DESC, topics.date DESC
    LIMIT  500 
  • COUNT(*) and COUNT(1) compile the same -- see EXPLAIN EXTENDED ...; SHOW WARNINGS; – Rick James Jul 15 '19 at 14:38
0

Joining the two tables and grouping will probably be more efficient if you have appropriate indexes defined:

SELECT topics.name, topics.topic_id, topics.creator, COUNT(answers.topic_id) AS replies
FROM topics 
LEFT OUTER JOIN answers ON answers.topic_id = topics.topic_id
GROUP BY topics.name, topics.topic_id, topics.creator
ORDER BY topics.bumped DESC, topics.date DESC LIMIT 500

Counting answers.topic_id instead of * or 1 should stop it counting 1 for row for topics that have exactly zero matching rows in answers as it won't count NULLs.

With regard to "if you have appropriate indexes defined" - as others have asked, if you update the question to provide details of the table and index structure you may get a more specific/detailed response.

  • 1
    1. Don't you think group by would required if u modifiy the query like. else it will give only one record for whole topic ids. 2. and count(null) will always give 0. if i am not wrong. – HiteshRathi Jul 15 '19 at 14:27
  • @HiteshRathi - [1] yes, not sure how I managed to miss the GROUP BY out of that. Now fixed. [2] COUNT(NULL) isn't what I'm meaning in that part: if the OUTER JOIN doesn't match anything in answers for any given topics.topic_id there would still be one wrote in the group but with columns from answers all NULL. You don't want to count those, but you do want something from topics or you would get no row for that group instead of one row with a zero count. – David Spillett Jul 16 '19 at 9:37

Not the answer you're looking for? Browse other questions tagged or ask your own question.