[rt-users] sphinx weirdness

[Arkadiusz Miśkiewicz]

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 )