I have a content type article, which can be flagged by anonymous users. I have a view of those articles (only 3 there) on main page and after the clearing the cache website is awfully slow.
I tracked down the problem using the webprofiler down to flag module and a query:
SELECT "flagging\_node\_field\_data\_\_flag\_counts"."count" AS "flagging\_node\_field\_data\_\_flag\_counts\_count", "node\_field\_data"."created" AS "node\_field\_data\_created", MIN(node\_field\_data.nid) AS "nid", MIN(flagging\_node\_field\_data.id) AS "flagging\_node\_field\_data\_id" FROM "node\_field\_data" "node\_field\_data" LEFT JOIN "flagging" "flagging\_node\_field\_data" ON node\_field\_data.nid = flagging\_node\_field\_data.entity\_id AND flagging\_node\_field\_data.flag\_id = 'like' LEFT JOIN "flag\_counts" "flagging\_node\_field\_data\_\_flag\_counts" ON flagging\_node\_field\_data.flag\_id = flagging\_node\_field\_data\_\_flag\_counts.flag\_id AND flagging\_node\_field\_data\_\_flag\_counts.entity\_id = flagging\_node\_field\_data.entity\_id INNER JOIN "node" "node" ON node\_field\_data.nid = node.nid WHERE ("node\_field\_data"."status" = '1') AND ("node\_field\_data"."type" IN ('article')) GROUP BY node.nid, flagging\_node\_field\_data\_\_flag\_counts\_count, node\_field\_data\_created ORDER BY "node\_field\_data\_created" DESC LIMIT 3 OFFSET 0
Which takes more than 11 seconds to execute. This is probably coming from a view which flag count field but there are only 3 articles shown in the view.
The problem is even worse in /blog subpage, where there are 5 views blocks all containing flag counts, loading there takes more than 45 seconds.
The only upside is that caching is saving this up. So only the first load of the day is slow and after that website runs smooth.
I tried to implement patches from similar issues in other threads here in issue threads but sadly it did not work and problem is still there.