Mysql 5.1 slow query in RT

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.

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

What RT version?

Sorry forgot to include that 3.6.6

Thanks
MikeOn 5/29/08, Jesse Vincent jesse@bestpractical.com wrote:

On Thu, May 29, 2008 at 11:20:33AM -0400, Mike Zupan wrote:

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

What RT version?

Added bug into mysql tracker http://bugs.mysql.com/bug.php?id=37680

Some developers suggest to test mysql 5.1.25On Thu, May 29, 2008 at 7:20 PM, Mike Zupan hijinks@gmail.com wrote:

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.


http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Best regards, Ruslan.

What are your indices on? Which columns?

Thank you,
Micah Gersten
onShore Networks
Internal Developer

Mike Zupan wrote: