match query needs optimization

Hi,

I've found in my slow_log that your match query is slow (1 day log)

Count: 4736  Time=2.87s (13590s)  Lock=0.04s (190s)  Rows=5.0 (23680), 
  SELECT r.nid, MATCH(r.body, r.title) AGAINST ('S') AS score 
FROM z_node_revisions r 
INNER JOIN z_node n ON r.nid = n.nid AND r.vid = n.vid  
INNER JOIN z_node_access  na ON na.nid = n.nid 
LEFT JOIN z_domain_access da ON n.nid = da.nid 
WHERE (na.grant_view >= N AND ((na.gid = N AND na.realm = 'S'))) 
 AND ((da.realm = "S" AND da.gid = N) OR (da.realm = "S" AND da.gid = N)) 
 AND (  MATCH(r.body, r.title) AGAINST ('S') AND n.status <> N AND r.nid <> N AND n.type IN ('S','S','S','S','S','S','S','S','S','S','S') )
GROUP BY n.nid 
ORDER BY score DESC, r.vid DESC 
LIMIT N, N

in module you wrote:

    $result = db_query_range("SELECT r.nid, MATCH(r.body, r.title) AGAINST ('%s') AS score FROM {node_revisions} r INNER JOIN {node} n ON r.nid = n.nid AND r.vid = n.vid INNER JOIN {term_node} t ON n.nid = t.nid AND t.tid IN (%s) WHERE MATCH(r.body, r.title) AGAINST ('%s') AND n.status <> 0 AND r.nid <> %d AND n.type IN ($types) GROUP BY n.nid ORDER BY score DESC, r.vid DESC", $text, $taxonomy_tids, $text, $node->nid, 0, variable_get('similar_num_display', 5));
  }
  else {
    $result = db_query_range("SELECT r.nid, MATCH(r.body, r.title) AGAINST ('%s') AS score FROM {node_revisions} r INNER JOIN {node} n ON r.nid = n.nid AND r.vid = n.vid WHERE MATCH(r.body, r.title) AGAINST ('%s') AND n.status <> 0 AND r.nid <> %d AND n.type IN ($types) GROUP BY n.nid ORDER BY score DESC, r.vid DESC", $text, $text, $node->nid, 0, variable_get('similar_num_display', 5));

well, what was weird for me was this second match in WHERE - i assumed that you use it as a flag to filter content with score 0.
unfortunately MySQL isn't smart enough to store match score and it runs match twice each time this query is executed.

simplest way to change this is to use HAVING for score.
below you can see slow log from next day after fixing this query.

Count: 182  Time=2.04s (372s)  Lock=0.05s (10s)  Rows=5.0 (910), 
SELECT r.nid, MATCH(r.body, r.title) AGAINST ('S') AS score 
FROM z_node_revisions r 
 INNER JOIN z_node n ON r.nid = n.nid AND r.vid = n.vid  
 INNER JOIN z_node_access na ON na.nid = n.nid 
 LEFT JOIN z_domain_access da ON n.nid = da.nid 
WHERE (na.grant_view >= N AND ((na.gid = N AND na.realm = 'S'))) 
 AND ((da.realm = "S" AND da.gid = N) OR (da.realm = "S" AND da.gid = N)) 
 AND (  n.status <> N AND r.nid <> N AND n.type IN ('S','S','S','S','S','S','S','S','S','S','S') )
GROUP BY n.nid 
HAVING score > N 
ORDER BY score DESC, r.vid DESC 
LIMIT N, N

while average time is still big, important thing is that there are 182 queries instead 4736 which means that remaining 4k+ weren't slow next day.
there was no big difference in traffic.

There is also one important thing - performance is better for match with long r.body values - when you compare results for few hundred chars theres no difference. but when you go for few k chars and try to match them with 7k nodes simillar length impact is huge - query is 10 times faster.

so fixed code looks as follows:

$result = db_query_range(db_rewrite_sql("SELECT r.nid, MATCH(r.body, r.title) AGAINST ('%s') AS score FROM {node_revisions} r INNER JOIN {node} n ON r.nid = n.nid AND r.vid = n.vid INNER JOIN {term_node} t ON n.nid = t.nid AND t.tid IN (%s) WHERE n.status <> 0 AND r.nid <> %d AND n.type IN ($types) GROUP BY n.nid HAVING score > 0 ORDER BY score DESC, r.vid DESC"), $text, $taxonomy_tids, $node->nid, 0, variable_get('similar_num_display', 5));
  }
  else {
    $result = db_query_range(db_rewrite_sql("SELECT r.nid, MATCH(r.body, r.title) AGAINST ('%s') AS score FROM {node_revisions} r INNER JOIN {node} n ON r.nid = n.nid AND r.vid = n.vid WHERE n.status <> 0 AND r.nid <> %d AND n.type IN ($types) GROUP BY n.nid HAVING score > 0 ORDER BY score DESC, r.vid DESC"), $text, $node->nid, 0, variable_get('similar_num_display', 5));
  }