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@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 -- RT Training - Boston, September 9-10 http://bestpractical.com/training
