[rt-users] Upgrade from 4.2.2 to 4.2.6 - simple search not using sphinx when searching

[Alex Vandiver]

On 07/29/2014 11:24 AM, Radu Tureac wrote:
> I have fulltext search implemented with sphinx and mysql 5.6.15.
> 
> The problem is that after upgrade, I started to get these large queries
> in mysql when using simple search:

RT 4.2.4 and above do a full-content search with simple search if
indexed full-test searching is enabled.

> # Time: 140725 13:15:31
> # User at Host: rt_user[rt_user] @ localhost []  Id:    26
> # Query_time: 10.390028  Lock_time: 0.000276 Rows_sent: 1
>  Rows_examined: 910059
> SET timestamp=1406283331;
> SELECT COUNT(DISTINCT main.id <http://main.id>) FROM Tickets main JOIN
> Transactions Transactions_1  ON ( Transactions_1.ObjectType =
> 'RT::Ticket' ) AND ( Transactions_1.ObjectId = main.id <http://main.id>
> ) LEFT JOIN Attachments Attachments_2  ON ( Attachments_2.TransactionId
> = Transactions_1.id ) LEFT JOIN AttachmentsIndex AttachmentsIndex_3  ON
> ( AttachmentsIndex_3.id = Attachments_2.id )  WHERE (main.IsMerged IS
> NULL) AND (main.Status != 'deleted') AND (main.Type = 'ticket') AND ( (
>  ( main.Subject LIKE '%*word*%' OR  ( AttachmentsIndex_3.query =
> '*word*;limit=10000;maxmatches=10000' AND Attachments_2.Filename IS NULL
> )  )  ) );

This failure is likely a failure of the Sphinx index to be picked up
correctly, and is a limitation of the Sphinx search engine.  You can
confirm this by showing the output of EXPLAIN on the above query.

> Is there any way that I could make the simple search use the sphinx
> engine by default for all the queries? Or any way to revert it?

You likely have two options:
 1. Use a local overlay to replace the HandleDefault function in
lib/RT/Search/Simple.pm with the version from 4.2.3 or earlier, which
only searches subject, not subject and content.
 2. Try the 4.2/mysql-native-fts branch, which uses the indexed
full-text search for InnoDB tables which is available in MySQL 5.6.

 - Alex