On Wednesday 25 of February 2015, Arkadiusz Miśkiewicz wrote: > 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)
Ok, mysql is too smart! For sphinx to work mysql needs to first query AttachmentsIndex_3 and then make joins to it. Otherwise sphinx won't work. That's due to sphinx architecture. Here mysql is too smart and differently optimizes query thus breaking sphinx support in rt. Now I've tried FORCE INDEX and such but wasn't able to force mysql to first query AttachmentsIndex_3. There is STRAIGHT_JOIN that forces joins orders, so maybe that is some solution. Other ideas? -- Arkadiusz Miśkiewicz, arekm / ( maven.pl | pld-linux.org )
