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

[Radu Tureac]

Thanks for your quick answer, Alex.

I got the Simple.pm from rt-4.2.3 and put it the local dir. Now it's back
to normal.

The explain looks like this:

+----+-------------+--------------------+------+---------------+---------------+---------+----------------------+-------+----------------------------------------------------+
| id | select_type | table              | type | possible_keys | key
    | key_len | ref                  | rows  | Extra
                       |
+----+-------------+--------------------+------+---------------+---------------+---------+----------------------+-------+----------------------------------------------------+
|  1 | SIMPLE      | main               | ALL  | PRIMARY       | NULL
   | NULL    | NULL                 | 41262 | Using where
                     |
|  1 | SIMPLE      | Transactions_1     | ref  | Transactions1 |
Transactions1 | 70      | const,rt.main.id     |     1 | Using where; Using
index                           |
|  1 | SIMPLE      | Attachments_2      | ref  | Attachments2  |
Attachments2  | 4       | rt.Transactions_1.id |     3 | Using where
                                 |
|  1 | SIMPLE      | AttachmentsIndex_3 | ALL  | NULL          | NULL
   | NULL    | NULL                 |    20 | Using where; Using join
buffer (Block Nested Loop) |
+----+-------------+--------------------+------+---------------+---------------+---------+----------------------+-------+----------------------------------------------------+

I will give the 4.2/mysql-native-fts branch a test run when I have time and
give you back some feedback.

Thanks again,
Radu


On Tue, Jul 29, 2014 at 8:10 PM, Alex Vandiver <alexmv at bestpractical.com>
wrote:

> 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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20140730/dd53e13c/attachment.htm>