# 禁用字母排序时标签查询缓慢

**URL:** <https://meta.discourse.org/t/slow-tags-query-when-alphabetical-sort-is-disabled/78133>\
**Category:** Development\
**Created:** [2018年一月16日 05:40 UTC](https://meta.discourse.org/t/slow-tags-query-when-alphabetical-sort-is-disabled/78133 "2018-01-16T05:40:44Z")\
**Posts on this page:** 17\
**Page:** 1

<div class="post-metadata">

**Author:** ![SimonWu](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/simonwu/32/85036_2.png) [@SimonWu](https://meta.discourse.org/u/SimonWu)\
**Post date:** [2018年一月16日 05:40 UTC](https://meta.discourse.org/t/slow-tags-query-when-alphabetical-sort-is-disabled/78133/1 "2018-01-16T05:40:44Z")

</div>

Hi,

I found a slow query during the test.

```plaintext
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:

 ![image](https://global.discourse-cdn.com/meta/original/3X/4/f/4f806b2a49a5c0d1aa1a3558f82f837f079ac84a.png)

And the table `topic_tags` has more than 3 million records. The query will run 4 seconds.

Regards,

---

<div class="post-metadata">

**Author:** ![mpalmer](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/mpalmer/32/45740_2.png) [@mpalmer](https://meta.discourse.org/u/mpalmer)\
**Post date:** [2018年一月16日 06:11 UTC](https://meta.discourse.org/t/slow-tags-query-when-alphabetical-sort-is-disabled/78133/2 "2018-01-16T06:11:28Z")

</div>

What does `EXPLAIN` show for the query, on your database?

---

<div class="post-metadata">

**Author:** ![SimonWu](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/simonwu/32/85036_2.png) [@SimonWu](https://meta.discourse.org/u/SimonWu)\
**Post date:** [2018年一月16日 06:24 UTC](https://meta.discourse.org/t/slow-tags-query-when-alphabetical-sort-is-disabled/78133/3 "2018-01-16T06:24:05Z")

</div>

Explain

 ![image](https://global.discourse-cdn.com/meta/original/3X/4/c/4c6c0d316c80e4edaddf8085f265223fe6d2fba7.png)

---

<div class="post-metadata">

**Author:** ![sam](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/sam/32/102149_2.png) [@sam](https://meta.discourse.org/u/sam)\
**Post date:** [2018年一月16日 07:08 UTC](https://meta.discourse.org/t/slow-tags-query-when-alphabetical-sort-is-disabled/78133/4 "2018-01-16T07:08:56Z")

</div>

@neil is working on improving tagging here so he may have already addressed this in his dev env.

---

<div class="post-metadata">

**Author:** ![neil](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/neil/32/102150_2.png) [@neil](https://meta.discourse.org/u/neil)\
**Post date:** [2018年一月18日 22:21 UTC](https://meta.discourse.org/t/slow-tags-query-when-alphabetical-sort-is-disabled/78133/6 "2018-01-18T22:21:51Z")

</div>

Improving this performance is a bit tricky. I think we’ll need to change the functionality of that filter. This case is easy and can be optimized now that we normalize the number of times a tag has been used:

 ![51 PM](https://global.discourse-cdn.com/meta/original/3X/0/a/0af72f252ea5d7981d4141f2f20e14ecfb1fcf8e.png)

But when scoped to a category, we currently re-count how many times every tag has been used in topics in that category and sub-categories and then sort.

 ![10 PM](https://global.discourse-cdn.com/meta/original/3X/7/2/72700d5fa74bed9f0be71076777676dc7b30f3c9.png)

Also included in the query is which categories you have permission to see. Tags allowed only in private categories shouldn’t be exposed in this filter.

One option is to only show tags used in the category, but sort by their global counts instead of category-specific counts.

---

<div class="post-metadata">

**Author:** ![sam](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/sam/32/102149_2.png) [@sam](https://meta.discourse.org/u/sam)\
**Post date:** [2018年一月18日 22:47 UTC](https://meta.discourse.org/t/slow-tags-query-when-alphabetical-sort-is-disabled/78133/7 "2018-01-18T22:47:09Z")

</div>

I just question the entire premise of this, is 3 million tags even a real use case?

I guess we could create a “category\_tag” table and keep it up to date then sort on that. But I really want to deal with some real world data here before going crazy.

---

<div class="post-metadata">

**Author:** ![codinghorror](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/codinghorror/32/110067_2.png) [@codinghorror](https://meta.discourse.org/u/codinghorror)\
**Post date:** [2018年一月19日 00:17 UTC](https://meta.discourse.org/t/slow-tags-query-when-alphabetical-sort-is-disabled/78133/11 "2018-01-19T00:17:39Z")

</div>

3 million tags isn’t _remotely_ a target in our software. We’re looking at more like 100,000 max certainly _FAR_ under a million.

---

<div class="post-metadata">

**Author:** ![david](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/david/32/157490_2.png) [@david](https://meta.discourse.org/u/david)\
**Post date:** [2018年一月19日 00:29 UTC](https://meta.discourse.org/t/slow-tags-query-when-alphabetical-sort-is-disabled/78133/12 "2018-01-19T00:29:58Z")

</div>

> [@SimonWu](#):
>
> `topic_tags` has more than 3 million records

I think 3 million in this case is the number of topic-tag relations, rather than the number of unique tags.

So that could be 100,000 tags, with 30 topics attached to each tag. That’s still very large, but not quite as unbelievable as 3 million unique tags.

---

<div class="post-metadata">

**Author:** ![sam](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/sam/32/102149_2.png) [@sam](https://meta.discourse.org/u/sam)\
**Post date:** [2018年一月19日 00:37 UTC](https://meta.discourse.org/t/slow-tags-query-when-alphabetical-sort-is-disabled/78133/13 "2018-01-19T00:37:04Z")

</div>

Well I guess we are going to need a table to track counts of tag per category, then we can cheaply figure this out.

---

<div class="post-metadata">

**Author:** ![SimonWu](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/simonwu/32/85036_2.png) [@SimonWu](https://meta.discourse.org/u/SimonWu)\
**Post date:** [2018年一月19日 02:31 UTC](https://meta.discourse.org/t/slow-tags-query-when-alphabetical-sort-is-disabled/78133/14 "2018-01-19T02:31:48Z")

</div>

@david , you’re right. It’s 20 unique tags. And 1~5 tags per topic.  
@sam, please refer to [https://stackoverflow.com/tags](https://stackoverflow.com/tags). You will see the real world data could be way more than 3 million records in topic\_tags table.

---

<div class="post-metadata">

**Author:** ![sam](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/sam/32/102149_2.png) [@sam](https://meta.discourse.org/u/sam)\
**Post date:** [2018年一月19日 02:37 UTC](https://meta.discourse.org/t/slow-tags-query-when-alphabetical-sort-is-disabled/78133/15 "2018-01-19T02:37:39Z")

</div>

I know that page, I remember hacking on that

---

<div class="post-metadata">

**Author:** ![SimonWu](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/simonwu/32/85036_2.png) [@SimonWu](https://meta.discourse.org/u/SimonWu)\
**Post date:** [2018年一月19日 03:20 UTC](https://meta.discourse.org/t/slow-tags-query-when-alphabetical-sort-is-disabled/78133/16 "2018-01-19T03:20:22Z")

</div>

Great to hear 🙂

---

<div class="post-metadata">

**Author:** ![neil](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/neil/32/102150_2.png) [@neil](https://meta.discourse.org/u/neil)\
**Post date:** [2018年二月13日 19:57 UTC](https://meta.discourse.org/t/slow-tags-query-when-alphabetical-sort-is-disabled/78133/17 "2018-02-13T19:57:47Z")

</div>

I committed a fix for this, so please give it a try.

---

<div class="post-metadata">

**Author:** ![SimonWu](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/simonwu/32/85036_2.png) [@SimonWu](https://meta.discourse.org/u/SimonWu)\
**Post date:** [2018年二月23日 06:22 UTC](https://meta.discourse.org/t/slow-tags-query-when-alphabetical-sort-is-disabled/78133/18 "2018-02-23T06:22:00Z")

</div>

Hi Neil. I try the latest bits. But I don’t find the tag dropdown after turning on `show filter by tag`.  
But /tags page looks good.

---

<div class="post-metadata">

**Author:** ![neil](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/neil/32/102150_2.png) [@neil](https://meta.discourse.org/u/neil)\
**Post date:** [2018年二月23日 16:57 UTC](https://meta.discourse.org/t/slow-tags-query-when-alphabetical-sort-is-disabled/78133/19 "2018-02-23T16:57:52Z")

</div>

Do you see any errors in your logs that mention “category\_tag\_stats”? There’s a job that runs which will initialize the stats needed by the dropdown. It might take a while to run on your database, but should have completed.

---

<div class="post-metadata">

**Author:** ![SimonWu](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/simonwu/32/85036_2.png) [@SimonWu](https://meta.discourse.org/u/SimonWu)\
**Post date:** [2018年二月26日 02:12 UTC](https://meta.discourse.org/t/slow-tags-query-when-alphabetical-sort-is-disabled/78133/20 "2018-02-26T02:12:39Z")

</div>

Yes, I can see it now. It will take a while since my forum has over 5M topics.

---

<div class="post-metadata">

**Author:** ![Jeremie\_Leroy](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/jeremie_leroy/32/84427_2.png) [@Jeremie\_Leroy](https://meta.discourse.org/u/Jeremie_Leroy)\
**Post date:** [2018年十月17日 17:49 UTC](https://meta.discourse.org/t/slow-tags-query-when-alphabetical-sort-is-disabled/78133/21 "2018-10-17T17:49:03Z")

</div>

Hi All,

Is there a way to sort this king of page by the title ?  
[https://francais-a-londres.org/tags/medicare-français](https://francais-a-londres.org/tags/medicare-fran%C3%A7ais)

Many thanks for your help