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
Comment #1
dawehnerCan you please provide an export ....
Comment #2
gregglesSome 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(); ?>
Comment #3
dawehnerThis seems to be similar to #404150: Improve performance of the "Ops" field in Views
Comment #4
gregglesIndeed a duplicate. I was searching for "twice" but not "two" nor "2".