Hello,
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:
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) FROM Tickets main JOIN Transactions
Transactions_1 ON ( Transactions_1.ObjectType = ‘RT::Ticket’ ) AND (
Transactions_1.ObjectId = 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 ) ) ) );
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?
I also mention that the content search is still working with sphinx as
before, if I write fulltext:word I can find the query in the
RT_ROOT/var/sphinx/query.log
[Wed Jun 25 16:41:45.057 2014] 0.002 sec 0.002 sec [all/0/rel 3 (0,10000)]
[rt,rtdelta] word
[Wed Jun 25 16:41:45.191 2014] 0.002 sec 0.002 sec [all/0/rel 3 (0,10000)]
[rt,rtdelta] word
[Wed Jun 25 16:41:58.256 2014] 0.002 sec 0.002 sec [all/0/rel 3 (0,10000)]
[rt,rtdelta] word
[Wed Jun 25 16:42:36.063 2014] 0.002 sec 0.002 sec [all/0/rel 3 (0,10000)]
[rt,rtdelta] word
Thank you!
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:
- 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.
- 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.
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,
RaduOn Tue, Jul 29, 2014 at 8:10 PM, Alex Vandiver alexmv@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@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:
- 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.
- 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.