1

I have about 350 000 records in node table and about 600 000 in node_access and domain_access tables.

When I try to open content tab it takes about a minute to load.

As I found out with Devel module this query takes about 32 seconds to execute:

SELECT DISTINCT node.nid          AS nid,
                node.title        AS node_title,
                node.language     AS node_language,
                history.timestamp AS history_timestamp,
                node.created      AS node_created,
                node.changed      AS node_changed,
                node.type         AS node_type,
                users_node.name   AS users_node_name,
                users_node.uid    AS users_node_uid,
                node.status       AS node_status
FROM node node
         LEFT JOIN users users_node ON node.uid = users_node.uid
         LEFT JOIN taxonomy_index taxonomy_index ON node.nid = taxonomy_index.nid
         LEFT JOIN taxonomy_term_data taxonomy_term_data_node ON taxonomy_index.tid = taxonomy_term_data_node.tid
         LEFT JOIN history history ON node.nid = history.nid AND history.uid = :views_join_condition_
ORDER BY node_changed DESC
LIMIT 50 OFFSET 0

And this one takes 20 seconds:

SELECT COUNT(*) AS expression
FROM (SELECT DISTINCT node.nid          AS nid,
                      node.title        AS node_title,
                      node.language     AS node_language,
                      history.timestamp AS history_timestamp,
                      node.created      AS node_created,
                      node.changed      AS node_changed,
                      node.type         AS node_type,
                      users_node.name   AS users_node_name,
                      users_node.uid    AS users_node_uid,
                      node.status       AS node_status,
                      1                 AS expression
      FROM node node
               LEFT JOIN users users_node ON node.uid = users_node.uid
               LEFT JOIN taxonomy_index taxonomy_index ON node.nid = taxonomy_index.nid
               LEFT JOIN taxonomy_term_data taxonomy_term_data_node ON taxonomy_index.tid = taxonomy_term_data_node.tid
               LEFT JOIN history history ON node.nid = history.nid AND history.uid = :views_join_condition_1) subquery

I tested the first query and found out that the ORDER_BY causes the slowness.

Is there any way to make it fast?

  • It’s almost certainly the LEFT JOINs. If you can find a way to change those to INNER JOINs, and the results are still useful, that’s probably your best bet. Doesn’t seem like the ORDER BY would be relevant to the speed - the count query doesn’t contain it and still takes 20 seconds. – Clive Dec 16 '19 at 8:24
  • you are right. One thing I noticed now is that DISTINCT causes the slowness. When I use GROUP BY instead of DISTINCT it works very fast, but I am not sure that this is right solution... – Dito Dec 16 '19 at 8:32
  • Yeah DISTINCT isn’t always brilliant for large tables, depending on the indexes, how it can identify the primary key, etc. If you’re getting the same results when using GROUP BY don’t overthink it - you’ve won :) – Clive Dec 16 '19 at 8:38
  • Well, I am getting the same result even without DISTINCT or GROUP BY. Should I remove both? – Dito Dec 16 '19 at 8:39
  • @Dito without DISTINCT or GROUP BY you will get duplicated entries because of the joins to taxonomy tables, when the same node has multiple terms. So best thing is to stick to GROUP BY. – Alfred Armstrong Dec 16 '19 at 11:44
0

I am not exactly sure if it is the best solution but it works for me. I changed default nodes view to use GROUP BY (without any aggregation function) instead of DISTINCT and it works 40 times faster in my case.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.