Multiple Tags search query takes too long ? - big forum


(Veer) #1

Its a very big forum, around 0.5 million topics and 10 thousands tags, which are on each topic, 3-4 in number.
Multiple tags search query takes very very long time, how to fix this ?

(Sam Saffron) #2

Can you paste in the slow query using mini profiler

(Veer) #3

This query took Two minutes to page load.

 [2017-01-20T10:22:28.898780 #31725]  INFO -- :   Parameters: {"q"=>"tag:tn tag:family-practice tag:female", "_"=>"1484907702241"}
D, [2017-01-20T10:22:28.904228 #31725] DEBUG -- :   User Load (1.6ms)  SELECT  "users".* FROM "users" WHERE "users"."auth_token" = '394d73bdbad819e5f109d189d83aa2bd' AND (auth_token_updated_at IS NULL OR auth_token_updated_at > '2016-11-21 10:22:28.901686')  ORDER BY "users"."id" ASC LIMIT 1  [["auth_token", "394d73bdbad819e5f109d189d83aa2bd"]]
D, [2017-01-20T10:22:28.910074 #31725] DEBUG -- :   Group Load (1.3ms)  SELECT "groups".* FROM "groups" WHERE "groups"."automatic" = 'f' AND (LENGTH(COALESCE(automatic_membership_email_domains, '')) > 0)  [["automatic", false]]
D, [2017-01-20T10:22:28.922002 #31725] DEBUG -- :    (2.1ms)  SELECT FROM "categories" WHERE "categories"."read_restricted" = 't'  [["read_restricted", true]]
D, [2017-01-20T10:22:53.729875 #31725] DEBUG -- : Delivering messages [] to client e4ddcaa7a6f840ea83479f186f1daf88 for user 3 (chunked)

Does multiple tag search support AND vs OR?
(cpradio) #4

Unfortunately, neither of those items listed is the query in question. Do you have mini-profiler open when running the search? Can you browse through it to see what query in particular is taking long (and copy its results here)?

(Veer) #5

here is the complete miniprofiler details.
please see

(cpradio) #6

I see. So in this example, it definitely is requiring each tag individually. I imagine the performance impact is the use of IN and then doing it 3 times (one for each tag).

I also wonder if the search can be smarter about this? Only use IN when multiple tags are provided, otherwise use =

If you alter your search to tags:wa,gastroenterology,female does it work better? Granted, it changes the query, as it won’t be topics that have all three, but topics that have any of the three.

Right now tags does not have a performant way to do AND and that is being requested at

ping @alehandrof, you might find this interesting

(Clay Heaton) #7

I’ll just throw in some food for thought here. It might be nice to have a URL structure to support searching by multiple tags.

This could be #wordpress AND #embedding

This could be #wordpress OR #embedding|embedding

(Rafael dos Santos Silva) #8

Topics tagged docker & email

(Clay Heaton) #9

Cool! I didn’t know you could do that. Thanks!