Mysql 5.1 slow query in RT


#1

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.


#2

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?


#3

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?


#4

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.


#5

What are your indices on? Which columns?

Thank you,
Micah Gersten
onShore Networks
Internal Developer

Mike Zupan wrote: