Search performance improvement: Extra join in do_search coming from node_search
robertDouglass - May 11, 2008 - 22:41
| Project: | Drupal |
| Version: | 5.x-dev |
| Component: | node.module |
| Category: | bug report |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | patch (to be ported) |
Description
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 |
|---|---|
| extra-join.patch | 910 bytes |
#1
I have also taken this join out in the Faceted Search module, with no known adverse effects.
#2
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!
#3
I've also reviewed 5.x and 6.x and think that this can be backported, given that a few more eyes look at it and do some testing.
#4
I gave the patch a go on my 5.3 version site. Looks good to me.
#5
I've committed this to CVS HEAD. Thanks Robert. If Gabor wants to backport it to D6, he's welcome to do so.
#6
Does not apply against Drupal 6, needs a reroll.
#7
Changing status.
#8
Re-rolled for D6.
#9
#10
ha. David and I were working on it simultaneously.
#11
I'll re-join the Search Sprint chat room... :)
Both patches are identical.
#12
I confirmed that the patch applies to the DRUPAL-6 tag and I tested search on the 100k node database and the patch returns the expected results.
#13
Thanks, committed to D6.
#14
Given that performance for search on D5 is still a hot topic this should get ported further.