Hi.
rt 4.0.10 + mysql 5.6.22 + sphinx 2.2.7
rt uses such query:
mysql> 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 '%inconventus%' OR (
AttachmentsIndex_3.query = 'inconventus' ) ) ) AND ( main.Status = 'new' OR
main.Status = 'open' OR main.Status = 'stalled' ) );
+-------------------------+
| COUNT(DISTINCT main.id) |
+-------------------------+
| 0 |
+-------------------------+
1 row in set (0.16 sec)
mysql> EXPLAIN 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
'%inconventus%' OR ( AttachmentsIndex_3.query = 'inconventus' ) ) ) AND (
main.Status = 'new' OR main.Status = 'open' OR main.Status = 'stalled' ) );
+----+-------------+--------------------+-------+------------------------+----------------+---------+-----------------------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key
| key_len | ref | rows | Extra
|
+----+-------------+--------------------+-------+------------------------+----------------+---------+-----------------------+------+----------------------------------------------------+
| 1 | SIMPLE | main | range | PRIMARY,tickets_status |
tickets_status | 195 | NULL | 2228 | Using index
condition; Using where |
| 1 | SIMPLE | Transactions_1 | ref | Transactions1 |
Transactions1 | 70 | const,rt3.main.id | 1 | Using where; Using
index |
| 1 | SIMPLE | Attachments_2 | ref | Attachments2 |
Attachments2 | 4 | rt3.Transactions_1.id | 4 | Using index
|
| 1 | SIMPLE | AttachmentsIndex_3 | ALL | NULL | NULL
| NULL | NULL | 20 | Using where; Using join
buffer (Block Nested Loop) |
+----+-------------+--------------------+-------+------------------------+----------------+---------+-----------------------+------+----------------------------------------------------+
4 rows in set (0.00 sec)
and as you see above it finds nothing BUT if I remove
"main.Subject LIKE '%inconventus%' OR" from query it finds one ticket, why? :
mysql> 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 ( ( ( ( AttachmentsIndex_3.query = 'inconventus' )
) ) AND ( main.Status = 'new' OR main.Status = 'open' OR main.Status =
'stalled' ) );
+-------------------------+
| COUNT(DISTINCT main.id) |
+-------------------------+
| 1 |
+-------------------------+
1 row in set (0.01 sec)
mysql> EXPLAIN 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 ( ( ( ( AttachmentsIndex_3.query =
'inconventus' ) ) ) AND ( main.Status = 'new' OR main.Status = 'open' OR
main.Status = 'stalled' ) );
+----+-------------+--------------------+--------+------------------------+---------+---------+---------------------------------+------+-----------------------------------+
| id | select_type | table | type | possible_keys | key
| key_len | ref | rows | Extra
|
+----+-------------+--------------------+--------+------------------------+---------+---------+---------------------------------+------+-----------------------------------+
| 1 | SIMPLE | AttachmentsIndex_3 | ref | query |
query | 767 | const | 3 | Using where with
pushed condition |
| 1 | SIMPLE | Attachments_2 | eq_ref | PRIMARY,Attachments2 |
PRIMARY | 4 | rt3.AttachmentsIndex_3.id | 1 | Using where
|
| 1 | SIMPLE | Transactions_1 | eq_ref | PRIMARY,Transactions1 |
PRIMARY | 4 | rt3.Attachments_2.TransactionId | 1 | Using where
|
| 1 | SIMPLE | main | eq_ref | PRIMARY,tickets_status |
PRIMARY | 4 | rt3.Transactions_1.ObjectId | 1 | Using where
|
+----+-------------+--------------------+--------+------------------------+---------+---------+---------------------------------+------+-----------------------------------+
4 rows in set (0.00 sec)
Any idea what is going on? Additional subject searching shouldn't cause such
problem
since it's ORed with the rest of that part of query.
--
Arkadiusz Miśkiewicz, arekm / ( maven.pl | pld-linux.org )
