I've built a view that has exposed filters for whether nodes are flagged or not and has quick links to flag/unflag content in the view.

The query views generated is:

SELECT node.nid AS nid,
   users.name AS users_name,
   users.uid AS users_uid,
   node_data_field_related_project.field_related_project_nid AS node_data_field_related_project_field_related_project_nid,
   node.type AS node_type,
   node.vid AS node_vid,
   node.title AS node_title,
   node_data_field_time.field_time_value AS node_data_field_time_field_time_value,
   node.created AS node_created,
   node_data_field_time.field_community_education_value AS node_data_field_time_field_community_education_value,
   flag_content.content_id AS flag_content_content_id
 FROM node node 
 LEFT JOIN flag_content flag_content_node ON node.nid = flag_content_node.content_id AND flag_content_node.fid = 2
 INNER JOIN users users ON node.uid = users.uid
 LEFT JOIN content_field_related_project node_data_field_related_project ON node.vid = node_data_field_related_project.vid
 LEFT JOIN content_type_time node_data_field_time ON node.vid = node_data_field_time.vid
 LEFT JOIN flag_content flag_content ON node.nid = flag_content.content_id AND flag_content.fid = 2
 WHERE (node.status <> 0) AND (node.type = 'time') AND (flag_content_node.uid IS NOT NULL)
   ORDER BY node_created DESC 

This query runs in 2 minutes. The biggest problem seems to be that it is joining to flag_content twice. If I remove one of the joins to flag_content then it runs in less than a second. This wasn't a problem when the site was built and slowly became a problem now that we've got ~15,000 nodes and ~10,000 of those are flagged.

I'm not sure if this should be a views issue or a flag issue, but wanted to start an issue to track it.

Comments

dawehner’s picture

Can you please provide an export ....

greggles’s picture

Project: Views (for Drupal 7) » Flag
Component: Miscellaneous » Views integration

Some more details:

* The view is a VBO view with an operation to mark as flagged or not
* The view had 1 relationship to the flag table used by both the exposed filter and the field
* I tried removing the field and the filter, the query got a lot faster
* I added back to the filter, the query was fine
* I added back the field and it got slow again

So, I guess the problem is in the flag_handler_field_ops.inc

That field currently creates a new join every time, but perhaps could use <? $this->ensure_my_table(); ?>

dawehner’s picture

greggles’s picture

Status: Active » Closed (duplicate)

Indeed a duplicate. I was searching for "twice" but not "two" nor "2".