Performance 232x increase with One Letter

I was testing this module for 5.1 and observed some very slow times against my 2mm node system. In mysql I ran SHOW FULL PROCESSLIST, during the query to get the full sql, which is here:

(SELECT n.nid, n.title,n.type,nr.body,n.status,n.sticky,n.created FROM node n INNER JOIN node_revisions nr ON n.vid = nr.vid WHERE MATCH (n.title,nr.body) AGAINST ('+gilroy ' IN BOOLEAN MODE) AND n.status = 1) UNION (SELECT n.nid, n.title,n.type,nr.body,n.status,n.sticky,n.created FROM node n INNER JOIN comments c ON n.nid = c.nid INNER JOIN node_revisions nr ON n.vid = nr.vid WHERE MATCH (c.subject,c.comment) AGAINST ('+gilroy ' IN BOOLEAN MODE) AND n.status = 1 AND c.status = 0) ORDER BY sticky DESC,created DESC LIMIT 0, 500;

Speed: 2 min 16.94 sec) = 136.94 seconds

Began to tear the sql apart and observed this segment as very slow:

select * from node_revisions nr, node n WHERE MATCH (n.title,nr.body) AGAINST ('+gilroy ' IN BOOLEAN MODE);

This strange thing was the 'MATCH (n.title,nr.body)'. I ran this with each part dropped, title or body and the query ran significantly faster.

Next I decided to test pulling the title from 'node_revisions' rather than node.

(1) ALTER TABLE node_revisions DROP INDEX noderev_ft;
(2) CREATE FULLTEXT INDEX noderev_ft ON node_revisions (title,body);

I then ran the full query again with MATCH(nr.title,nr.body)

(SELECT n.nid, n.title,n.type,nr.body,n.status,n.sticky,n.created FROM node n INNER JOIN node_revisions nr ON n.vid = nr.vid WHERE MATCH (nr.title,nr.body) AGAINST ('+gilroy ' IN BOOLEAN MODE) AND n.status = 1) UNION (SELECT n.nid, n.title,n.type,nr.body,n.status,n.sticky,n.created FROM node n INNER JOIN comments c ON n.nid = c.nid INNER JOIN node_revisions nr ON n.vid = nr.vid WHERE MATCH (c.subject,c.comment) AGAINST ('+gilroy ' IN BOOLEAN MODE) AND n.status = 1 AND c.status = 0) ORDER BY sticky DESC,created DESC LIMIT 0, 500;

It now ran in .59 seconds or 232 times faster.

Finally I change the code in the module:

LINE 1352
$query->wheres[] = $exclusion ."MATCH (nr.title,nr.body) AGAINST ('". $query->search_string ."' IN BOOLEAN MODE)";

- Did I miss something obvious in my logic? Is there a reason we use node.title and not node_revisions.title in the query?

- I'm not much of a patch guy, but I thought some of you might want to know.

- I'll continue testing.

James