[rt-users] mysql 5.1 slow query in RT

[Mike Zupan]

We moved our RT server from mysql 4.0 to 5.1 and we are having some slow
query issues on the Attachments table not finding the correct index


mysql> explain SELECT main.* FROM Attachments main WHERE (main.Parent =
'308267') AND (main.ContentType = 'text/plain') ORDER BY main.id ASC;
+----+-------------+-------+-------+----------------------------------+---------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref |
rows | Extra |
+----+-------------+-------+-------+----------------------------------+---------+---------+------+---------+-------------+
| 1 | SIMPLE | main | index | Attachments3,Attachments4,Parent | PRIMARY | 4
| NULL | 1321158 | Using where |
+----+-------------+-------+-------+----------------------------------+---------+---------+------+---------+-------------+
1 row in set (0.00 sec)


mysql> explain SELECT main.* FROM Attachments main FORCE INDEX(Attachments3)
WHERE (main.Parent = '308267') AND (main.ContentType = 'text/plain') ORDER
BY main.id ASC;
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref |
rows | Extra |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-----------------------------+
| 1 | SIMPLE | main | ref | Attachments3 | Attachments3 | 4 | const | 1 |
Using where; Using filesort |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-----------------------------+
1 row in set (0.00 sec)


It works if I choose to force the index.

I have been trying to find where in the code this is to at least force the
index but I have not found it.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20080529/fe20447c/attachment.htm>