Search performance improvement: Extra join in do_search coming from node_search

Posted by robertDouglass on May 11, 2008 at 10:41pm

Issue Summary

LIVE FROM THE MINNESOTA SEARCH SPRINT

The old queries:

SELECT SUM(i.score * t.count) AS score FROM search_index i INNER JOIN search_total t 
  ON i.word = t.word 
  INNER JOIN node n ON n.nid = i.sid 
  INNER JOIN users u ON n.uid = u.uid 
  WHERE n.status = 1 
    AND (i.word = 'drupal' OR i.word = 'rocks') 
    AND i.type = 'node' GROUP BY i.type, i.sid 
  HAVING COUNT(*) >= 2 ORDER BY score DESC LIMIT 0, 1;            


SELECT i.type, i.sid, 
  10 * (3.38941376736 * SUM(i.score * t.count)) +
  5 * POW(2, (GREATEST(MAX(n.created), MAX(n.changed), MAX(c.last_comment_timestamp)) - 1038678891) * 6.43e-8) + 
  1 * (2.0 - 2.0 / (1.0 + MAX(c.comment_count) * 0.00271002710027)) AS score 
FROM search_index i 
INNER JOIN search_total t ON i.word = t.word 
INNER JOIN node n ON n.nid = i.sid 
INNER JOIN users u ON n.uid = u.uid
LEFT JOIN node_comment_statistics c ON c.nid = i.sid 
WHERE n.status = 1 
  AND (i.word = 'drupal' OR i.word = 'rocks') 
  AND i.type = 'node' 
GROUP BY i.type, i.sid HAVING COUNT(*) >= 2 
ORDER BY score DESC LIMIT 0, 10 

In both cases, the clause INNER JOIN users u ON n.uid = u.uid is superfluous. Taking it out produces identical results and executes faster.

Attachment	Size	Status	Test result	Operations
extra-join.patch	910 bytes	Ignored: Check issue status.	None	None

Comments

#1

Posted by David Lesieur on May 11, 2008 at 10:54pm

I have also taken this join out in the Faceted Search module, with no known adverse effects.

#2

Posted by douggreen on May 11, 2008 at 11:09pm

Status:

needs review

» reviewed & tested by the community

I reviewed and agree that this is superfluous. None of the joins use user columns. I checked all of the {node}, {search_index} clauses, the clauses from the node-ranks such as {node_comment_statistics} and {node_count}, and the clauses from the advanced search form such as {term_node}. It passes all existing tests plus the new tests in #145242.

Great catch Robert! Marking as RTBC!