Thanks for posting so much detail on your problem.
I thought that this might be the answer for me as I am experiencing exactly
the same issue!
I’m running rt 4.2.3 with MariaDB and Sphinx.
Searching ticket content from bash using the “search” command returns plenty
of results but the web search function returns 0!
I’ve enabled the statement logging as suggested and disable query caching to
see if this might make a difference but to no avail.
I expect you’re running into one of the situations where MySQL chooses
to not use the magic index that causes it to talk to the Sphinx indexer,
and instead looks at the contents of the AttachmentsIndex table
row-by-row – which is empty. You can determine if this is the case by
first determining the SQL your query produces; first, go to the
“Advanced” tab in RT, and copy the TicketSQL you’re running.
Then, from the command line, feed it to the following snippet to show
what RT is generating for the database; adjust the last line to match
your TicketSQL query:
perl -I/opt/rt4/lib -MRT=-init -le ‘$t=RT::Tickets->new(RT->SystemUser);
$t->FromSQL(“@ARGV”); print $t->BuildSelectQuery;’
“Content LIKE ‘moose’”
Take the SQL that generates, and feed it to MySQL, prefixing with EXPLAIN:
mysql> EXPLAIN SELECT DISTINCT main.*
FROM Tickets main
JOIN Transactions Transactions_1
ON ( Transactions_1.ObjectType = ‘RT::Ticket’ )
AND ( Transactions_1.ObjectId = main.id )
JOIN Attachments Attachments_2
ON ( Attachments_2.TransactionId = Transactions_1.id )
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 ( ( AttachmentsIndex_3.query
= ‘moose;limit=10000;maxmatches=10000’ ) );
This will return something like (omitting most columns for brevity):
| table | key |
| AttachmentsIndex_3 | query |
| Attachments_2 | PRIMARY |
| Transactions_1 | PRIMARY |
| main | NULL |
If the AttachmentsIndex table does not list “query” as the chosen key,
then MySQL’s query planner has decided to not use the magic Sphinx index.
I’m struggling to work out what my next step would be and if it wasn’t so
hard I’d be tempted to migrate over to PostgreSQL (that seems even more hard
work).
Do you guys have any suggestions?
If you were on plain MySQL, and not MariaDB, I’d suggest the new
4.2/mysql-native-fts branch that is currently in testing, as described
at Carbon60: Managed Cloud Services I do not know how
it will work in the context of MariaDB, however.
