Slow queries

# Query_time: 76.027677 Lock_time: 0.000148 Rows_sent: 26 Rows_examined: 139613800 SET timestamp=1675077108; SELECT DISTINCT "node_field_revision"."langcode" AS "node_field_revision_langcode", "users_field_data_node_revision"."langcode" AS "users_field_data_node_revision_langcode", "node_field_revision"."changed" AS "node_field_revision_changed", "node_field_revision"."vid" AS "vid", "node_field_data_node_field_revision"."nid" AS "node_field_data_node_field_revision_nid", "users_field_data_node_revision"."uid" AS "users_field_data_node_revision_uid" FROM "node_field_revision" "node_field_revision" LEFT JOIN "node_field_data" "node_field_data_node_field_revision" ON (EXISTS (SELECT "na"."nid" AS "nid" FROM "node_access" "na" WHERE ((("gid" IN ('0')) AND ("realm" = 'all')) OR (("gid" IN ('433')) AND ("realm" = 'view_unpublished_author')) OR (("gid" IN ('1')) AND ("realm" = 'view_unpublished_published_content')) OR (("gid" IN ('1')) AND ("realm" = 'view_unpublished_content'))) AND ("na"."grant_view" >= '1') AND ("na"."fallback" = '1') AND (("node_field_data_node_field_revision"."nid" = "na"."nid")))) AND ((node_field_revision.nid = node_field_data_node_field_revision.nid AND node_field_data_node_field_revision.langcode = node_field_revision.langcode)) INNER JOIN "node_revision" "node_revision" ON node_field_revision.vid = node_revision.vid INNER JOIN "users_field_data" "users_field_data_node_revision" ON node_revision.revision_uid = users_field_data_node_revision.uid LEFT JOIN "node_field_revision" "node_field_revision2" ON node_field_revision.nid = node_field_revision2.nid AND node_field_revision2.vid > node_field_revision.vid WHERE "node_field_revision2"."nid" IS NULL ORDER BY "node_field_revision"."changed" DESC LIMIT 26 OFFSET 0;

Comments & Activities

Issue created by @nicrodgers

Comment almost 2 years ago →

🇬🇧United Kingdom nicrodgers Monmouthshire, UK

Here's the output from EXPLAIN'ing that query:

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: node_field_revision
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1556908
     filtered: 100.00
        Extra: Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: node_revision
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY,node_field__revision_uid__target_id
          key: PRIMARY
      key_len: 4
          ref: drupal8.node_field_revision.vid
         rows: 1
     filtered: 100.00
        Extra: Using where
*************************** 3. row ***************************
           id: 1
  select_type: PRIMARY
        table: users_field_data_node_revision
   partitions: NULL
         type: ref
possible_keys: PRIMARY,user__id__default_langcode__langcode
          key: PRIMARY
      key_len: 4
          ref: drupal8.node_revision.revision_uid
         rows: 1
     filtered: 100.00
        Extra: Using index
*************************** 4. row ***************************
           id: 1
  select_type: PRIMARY
        table: node_field_data_node_field_revision
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY,node__id__default_langcode__langcode
          key: PRIMARY
      key_len: 18
          ref: drupal8.node_field_revision.nid,drupal8.node_field_revision.langcode
         rows: 1
     filtered: 100.00
        Extra: Using where; Using index
*************************** 5. row ***************************
           id: 1
  select_type: PRIMARY
        table: node_field_revision2
   partitions: NULL
         type: ref
possible_keys: PRIMARY,node__id__default_langcode__langcode
          key: node__id__default_langcode__langcode
      key_len: 4
          ref: drupal8.node_field_revision.nid
         rows: 40
     filtered: 100.00
        Extra: Using where; Not exists; Using index; Distinct
*************************** 6. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: na
   partitions: NULL
         type: ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: drupal8.node_field_data_node_field_revision.nid
         rows: 2
     filtered: 2.41
        Extra: Using where
6 rows in set, 2 warnings (0.00 sec)

Comment about 1 year ago →
heddn Nicaragua
I'm seeing drastic performance issues from this module as well. What is more, because it uses node grants, NodeAccessGrantsCacheContext marks all nodes as non-cacheable. So bad and worse because we can't even cache away the bad performance.
Comment 10 months ago →
🇩🇪Germany szeidler Berlin
We are experiencing the same issue after start using the module.

Problem/Motivation

Comments & Activities