<div dir="ltr">Thanks for your quick answer, Alex.<div><br></div><div><div>I got the Simple.pm from rt-4.2.3 and put it the local dir. Now it's back to normal.<br></div></div><div><br></div><div>The explain looks like this:</div>
<div><br></div><div><div>+----+-------------+--------------------+------+---------------+---------------+---------+----------------------+-------+----------------------------------------------------+</div><div>| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |</div>
<div>+----+-------------+--------------------+------+---------------+---------------+---------+----------------------+-------+----------------------------------------------------+</div><div>| 1 | SIMPLE | main | ALL | PRIMARY | NULL | NULL | NULL | 41262 | Using where |</div>
<div>| 1 | SIMPLE | Transactions_1 | ref | Transactions1 | Transactions1 | 70 | const,<a href="http://rt.main.id">rt.main.id</a> | 1 | Using where; Using index |</div><div>
| 1 | SIMPLE | Attachments_2 | ref | Attachments2 | Attachments2 | 4 | <a href="http://rt.Transactions_1.id">rt.Transactions_1.id</a> | 3 | Using where |</div>
<div>| 1 | SIMPLE | AttachmentsIndex_3 | ALL | NULL | NULL | NULL | NULL | 20 | Using where; Using join buffer (Block Nested Loop) |</div><div>+----+-------------+--------------------+------+---------------+---------------+---------+----------------------+-------+----------------------------------------------------+</div>
</div><div><br></div><div>I will give the <span style="font-family:arial,sans-serif;font-size:13px">4.2/mysql-native-fts branch a test run when I have time and give you back some feedback.</span></div><div><br></div><div>
Thanks again,</div><div>Radu</div></div><div class="gmail_extra"><br><br><div class="gmail_quote">On Tue, Jul 29, 2014 at 8:10 PM, Alex Vandiver <span dir="ltr"><<a href="mailto:alexmv@bestpractical.com" target="_blank">alexmv@bestpractical.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div class="">On 07/29/2014 11:24 AM, Radu Tureac wrote:<br>
> I have fulltext search implemented with sphinx and mysql 5.6.15.<br>
><br>
> The problem is that after upgrade, I started to get these large queries<br>
> in mysql when using simple search:<br>
<br>
</div>RT 4.2.4 and above do a full-content search with simple search if<br>
indexed full-test searching is enabled.<br>
<div class=""><br>
> # Time: 140725 13:15:31<br>
> # User@Host: rt_user[rt_user] @ localhost [] Id: 26<br>
> # Query_time: 10.390028 Lock_time: 0.000276 Rows_sent: 1<br>
> Rows_examined: 910059<br>
> SET timestamp=1406283331;<br>
</div>> SELECT COUNT(DISTINCT <a href="http://main.id" target="_blank">main.id</a> <<a href="http://main.id" target="_blank">http://main.id</a>>) FROM Tickets main JOIN<br>
<div class="">> Transactions Transactions_1 ON ( Transactions_1.ObjectType =<br>
</div>> 'RT::Ticket' ) AND ( Transactions_1.ObjectId = <a href="http://main.id" target="_blank">main.id</a> <<a href="http://main.id" target="_blank">http://main.id</a>><br>
<div class="">> ) LEFT JOIN Attachments Attachments_2 ON ( Attachments_2.TransactionId<br>
> = Transactions_1.id ) LEFT JOIN AttachmentsIndex AttachmentsIndex_3 ON<br>
> ( AttachmentsIndex_3.id = Attachments_2.id ) WHERE (main.IsMerged IS<br>
> NULL) AND (main.Status != 'deleted') AND (main.Type = 'ticket') AND ( (<br>
</div>> ( main.Subject LIKE '%*word*%' OR ( AttachmentsIndex_3.query =<br>
> '*word*;limit=10000;maxmatches=10000' AND Attachments_2.Filename IS NULL<br>
> ) ) ) );<br>
<br>
This failure is likely a failure of the Sphinx index to be picked up<br>
correctly, and is a limitation of the Sphinx search engine. You can<br>
confirm this by showing the output of EXPLAIN on the above query.<br>
<div class=""><br>
> Is there any way that I could make the simple search use the sphinx<br>
> engine by default for all the queries? Or any way to revert it?<br>
<br>
</div>You likely have two options:<br>
1. Use a local overlay to replace the HandleDefault function in<br>
lib/RT/Search/Simple.pm with the version from 4.2.3 or earlier, which<br>
only searches subject, not subject and content.<br>
2. Try the 4.2/mysql-native-fts branch, which uses the indexed<br>
full-text search for InnoDB tables which is available in MySQL 5.6.<br>
<span class="HOEnZb"><font color="#888888"><br>
- Alex<br>
</font></span></blockquote></div><br></div>