In my slow query log, I noticed several views queries that were using the {node_access} table without an index. I think the intended query plan is to join the node access table onto the node table so that the primary index (nid, gid, realm) of the node access table could be used, however EXPLAIN indicates that the join is happening in the opposite direction. As such, there is no index on the node access table that is useful since the where clause does not limit by nid.

I suggest recommending an index (gid, realm) on the node access table. On my installation this was able to reduce the estimated rows from the node access table from around 4500 (the whole table) to just under 3000, but results are likely heavily dependent on the node access modules used.

Comments

josepvalls’s picture

josepvalls commented

I can see several entries on my slow queries log also related to views queries using the node_access table.
Did you experiment with the index you mention further?
Any other thoughts?
I am using ACL and Content Access.

gapple’s picture

gapple
he/they
Primary language English
commented

@josepvalls, run the queries in the slow query log through EXPLAIN (I used mysqlsla for this). It's important to determine if it's in the slow query log because it's just slow, or if it's actually not using an index (and the mysql log_queries_not_using_indexes option is set).
You can try adding the index to your table and see if there is a change in the rows examined for the query's EXPLAIN.

I only have one site so far that has a pretty large node_access table where this index may be useful, so I would appreciate feedback from others; the usefulness of the index is likely quite dependent on your node access rules.