[rt-users] Ticket Query causes mysql to spin endlessly

[Roy El-Hames]

Jesse is right.
Below is the sql to search for content test or subject test in queue bla 
and Status != rejected;
SELECT COUNT(DISTINCT main.id) FROM Tickets main , Transactions 
Transactions_1, Attachments Attachments_2  WHERE 
((Transactions_1.ObjectType = 'RT::Ticket')) AND ((main.EffectiveId = 
main.id)) AND ((main.Status != 'deleted')) AND ((main.Type = 'ticket')) 
AND ( ( (main.Subject LIKE '%test%')or ( (Attachments_2.Content LIKE 
'%test%')AND(Attachments_2.TransactionId = Transactions_1.id)AND(main.id 
= Transactions_1.ObjectId) )  ) AND(main.Queue = '37')AND(main.Status != 
'rejected'));

an explain gives:
id | select_type | table          | type  | 
possible_keys                      | key           | key_len | ref   | 
rows    | Extra                    |
+----+-------------+----------------+-------+------------------------------------+---------------+---------+-------+---------+--------------------------+
|  1 | SIMPLE      | main           | range | 
PRIMARY,Tickets1,Tickets4,Tickets5 | Tickets1      |      15 | NULL  
|    1545 | Using where              |
|  1 | SIMPLE      | Transactions_1 | ref   | 
PRIMARY,Transactions1              | Transactions1 |      64 | const |  
504953 | Using where; Using index |
|  1 | SIMPLE      | Attachments_2  | ALL   | 
Attachments2                       | NULL          |    NULL | NULL  | 
1049069 | Using where              |
+----+-------------+----------------+-------+------------------------------------+---------------+---------+-------+---------+--------------------------+

what I found puzzling its not using any of the indexes on Attachamnet , 
these are there :

show index in Attachments;
+-------------+------------+--------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| Table       | Non_unique | Key_name     | Seq_in_index | Column_name   
| Collation | Cardinality | Sub_part | Packed | Null | Index_type | 
Comment |
+-------------+------------+--------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| Attachments |          0 | PRIMARY      |            1 | id            
| A         |     1396546 |     NULL | NULL   |      | BTREE      
|         |
| Attachments |          1 | Attachments2 |            1 | TransactionId 
| A         |     1396546 |     NULL | NULL   |      | BTREE      
|         |
| Attachments |          1 | Attachments3 |            1 | Parent        
| A         |          33 |     NULL | NULL   |      | BTREE      
|         |
| Attachments |          1 | Attachments3 |            2 | TransactionId 
| A         |     1396546 |     NULL | NULL   |      | BTREE      
|         |
+-------------+------------+--------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
Any ideas ??
(rt-3.4.4 and mysql  4.1.14 and dbix: 1.33)

Roy
Jesse Vincent wrote:

>
>On Tue, Nov 15, 2005 at 12:39:51PM -0800, Aaron Nichols wrote:
>  
>
>>On 11/15/05, Vance Vagell <vance.vagell at audiumcorp.com> wrote:
>>    
>>
>>> We also have this problem, where searching through the content of tickets
>>>causes the session to lock up.  If it is a lengthy search, the browser needs
>>>to be closed and the cookies deleted.  If it a short search, it returns
>>>results and has no problems.  I have found that specify more search
>>>parameters speeds up the search (obviously), and that avoids this problem
>>>most of the time.  But there are times when you need a more general search,
>>>and then you gamble with locking up the session.
>>>      
>>>
>
>It does very much look like the query engine is generating an incorrect
>query (sticking the join inside an OR).
>_______________________________________________
>http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
>
>Be sure to check out the RT Wiki at http://wiki.bestpractical.com
>
>Download a free sample chapter of RT Essentials from O'Reilly Media at http://rtbook.bestpractical.com
>
>WE'RE COMING TO YOUR TOWN SOON - RT Training in Amsterdam, Boston and
>San Francisco - Find out more at http://bestpractical.com/services/training.html
>
>  
>