When you allow a flag to be used by anonymous users (e. g. a bookmark function for blog posts or products) and this is used a lot, the performance can slow down extremely, especially when you have a view rendering the entity teaser showing the users' flag status for each entity/node. In our case the view shows 6 items per page and the DB queries took between 20 and 30 seconds for this page.
<!--break-->I temporarily activated the web profiler and as result got that the query
SELECT f.entity_id AS entity_id, f.flag_id AS flag_id, f.global AS global
FROM flagging f
WHERE
(entity_type = :db_condition_placeholder_0)
AND (entity_id IN (:db_condition_placeholder_1))
AND ((global = :db_condition_placeholder_2) OR (uid = :db_condition_placeholder_3))
took between 2 and 4 seconds for each entity/node. I looked into the flagged table and have seen that it contains approx. 1,300,000 rows. After deleting all row with uid = 0 and created timestamp before 30 days ago and afterwards optimizing the table with still approx. 111,000 rows the sum of all DB queries has reduced to 10 milliseconds.
Optimize the query and table index entity_id__uid to match. The index currently contains the colums entity_id and uid while the query involves entity_type, entity_id and uid. Because entity_type is not included in the index it requires a full table scan for every single query. Furthermore as the variety of entity_type values is not as big as for the other values (mostly its value is node), adding it as first column to the index does not make so much sense.
Therefore, I suggest changing the order of the WHERE arguments by moving the entity_type down:
SELECT f.entity_id AS entity_id, f.flag_id AS flag_id, f.global AS global
FROM flagging f
WHERE
(entity_id IN (:db_condition_placeholder_1))
AND ((uid = :db_condition_placeholder_3) OR (global = :db_condition_placeholder_2))
AND (entity_type = :db_condition_placeholder_0)
This way the database first can use the index entity_id__uid and then perform a scan on the filtered result set instead of the whole table.
Needs review
Flag core
Not all content is available!
It's likely this issue predates Contrib.social: some issue and comment data are missing.