I found a slow query during the test.
SELECT COUNT(topics.id) AS count_topics_id, tags.id, tags.name AS tags_id_tags_name FROM "tags" LEFT JOIN topic_tags ON tags.id = topic_tags.tag_id LEFT JOIN topics ON topics.id = topic_tags.topic_id AND topics.deleted_at IS NULL WHERE (topics.category_id in (3,5,12,11,9,6,8,1,7,10,2,4)) GROUP BY tags.id, tags.name ORDER BY count_topics_id DESC LIMIT 30
This query will be effective by enabling
Show a dropdown a filter a topic list by tag. and disabling
Show tags in alphabetical order. Configuration as below:
And the table
topic_tags has more than 3 million records. The query will run 4 seconds.