Slow Queries

RT Users
1

Hi

I am noticing from my slow log file (5 secs) that the following query
on mysql 4.0.24 is taking a 11 secs

Query_time: 11 Lock_time: 0 Rows_sent: 299925 Rows_examined: 983836

use rt3;
SELECT * FROM Transactions where Transactions.type = ‘create’;

Now looking at the explain I see

mysql> explain SELECT * FROM Transactions where Transactions.type = ‘create’;
| table | type | possible_keys | key | key_len | ref | rows
| Extra |
| Transactions | ALL | NULL | NULL | NULL | NULL | 984572
| Using where |

It has to go thru about 1 million rows and not using any index.

Is there any index, that I can create, to improve this w/o hurting any
other query?

Asif Iqbal
PGP Key: 0xE62693C5 KeyServer: pgp.mit.edu

2

Resending my question in case anyone missed to catch it the first time around.On 10/9/06, Asif Iqbal vadud3@gmail.com wrote:

Hi

I am noticing from my slow log file (5 secs) that the following query
on mysql 4.0.24 is taking a 11 secs

Query_time: 11 Lock_time: 0 Rows_sent: 299925 Rows_examined: 983836

use rt3;
SELECT * FROM Transactions where Transactions.type = ‘create’;

Now looking at the explain I see

mysql> explain SELECT * FROM Transactions where Transactions.type = ‘create’;
±-------------±-----±--------------±-----±--------±-----±-------±------------+
| table | type | possible_keys | key | key_len | ref | rows
| Extra |
±-------------±-----±--------------±-----±--------±-----±-------±------------+
| Transactions | ALL | NULL | NULL | NULL | NULL | 984572
| Using where |
±-------------±-----±--------------±-----±--------±-----±-------±------------+

It has to go thru about 1 million rows and not using any index.

Is there any index, that I can create, to improve this w/o hurting any
other query?

–
Asif Iqbal
PGP Key: 0xE62693C5 KeyServer: pgp.mit.edu

Asif Iqbal
PGP Key: 0xE62693C5 KeyServer: pgp.mit.edu

3

AFAIK RT doesn’t execute such queries, I think you should look into
your customizations.On 10/22/06, Asif Iqbal vadud3@gmail.com wrote:

Resending my question in case anyone missed to catch it the first time around.

On 10/9/06, Asif Iqbal vadud3@gmail.com wrote:

Hi

I am noticing from my slow log file (5 secs) that the following query
on mysql 4.0.24 is taking a 11 secs

Query_time: 11 Lock_time: 0 Rows_sent: 299925 Rows_examined: 983836

use rt3;
SELECT * FROM Transactions where Transactions.type = ‘create’;

Now looking at the explain I see

mysql> explain SELECT * FROM Transactions where Transactions.type = ‘create’;
±-------------±-----±--------------±-----±--------±-----±-------±------------+
| table | type | possible_keys | key | key_len | ref | rows
| Extra |
±-------------±-----±--------------±-----±--------±-----±-------±------------+
| Transactions | ALL | NULL | NULL | NULL | NULL | 984572
| Using where |
±-------------±-----±--------------±-----±--------±-----±-------±------------+

It has to go thru about 1 million rows and not using any index.

Is there any index, that I can create, to improve this w/o hurting any
other query?

–
Asif Iqbal
PGP Key: 0xE62693C5 KeyServer: pgp.mit.edu

–
Asif Iqbal
PGP Key: 0xE62693C5 KeyServer: pgp.mit.edu

The rt-users Archives

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.

4

It is impossible to add an index without hurting the insert
queries; the question if it hurts it significantly enough for you to
notice. I would just try creating one and see what happens, you can
always delete it later if it makes the inserts/updates too slow.On Sun, 22 Oct 2006, Asif Iqbal wrote:

Resending my question in case anyone missed to catch it the first time
around.

On 10/9/06, Asif Iqbal vadud3@gmail.com wrote:

I am noticing from my slow log file (5 secs) that the following query
on mysql 4.0.24 is taking a 11 secs

Query_time: 11 Lock_time: 0 Rows_sent: 299925 Rows_examined: 983836

SELECT * FROM Transactions where Transactions.type = ‘create’;

mysql> explain SELECT * FROM Transactions where Transactions.type =
‘create’;
±-------------±-----±--------------±-----±--------±-----±-------±------------+
| table | type | possible_keys | key | key_len | ref | rows
| Extra |
±-------------±-----±--------------±-----±--------±-----±-------±------------+
| Transactions | ALL | NULL | NULL | NULL | NULL | 984572
| Using where |
±-------------±-----±--------------±-----±--------±-----±-------±------------+

It has to go thru about 1 million rows and not using any index.

Is there any index, that I can create, to improve this w/o hurting any
other query?

5

Here is another query that takes 6.95 secs

Count: 198 Time=6.95s (1377s) Lock=0.00s (0s) Rows=1.1 (224),
rt_user[rt_user]@localhost
SELECT DISTINCT main.* FROM (((Tickets main JOIN Groups Groups_1
ON ( Groups_1.Instance = main.id)) LEFT JOIN CachedGroupMembers
CachedGroupMembers_2 ON ( CachedGroupMembers_2.GroupId = Groups_1.id)
AND ( (CachedGroupMembers_2.GroupId !=
CachedGroupMembers_2.MemberId))) LEFT JOIN Users Users_3 ON (
Users_3.id = CachedGroupMembers_2.MemberId)) WHERE ((Groups_1.Domain
= ‘S’)) AND ((Groups_1.Type = ‘S’)) AND ((main.EffectiveId = main.id))
AND ((main.Status != ‘S’)) AND ((main.Type = ‘S’)) AND ( ( (
(Users_3.EmailAddress = ‘S’) ) ) AND ( (main.Status =
‘S’)OR(main.Status = ‘S’) ) ) ORDER BY main.Priority DESC LIMIT N

Is there a way to improve this?On 10/9/06, Asif Iqbal vadud3@gmail.com wrote:

Hi

I am noticing from my slow log file (5 secs) that the following query
on mysql 4.0.24 is taking a 11 secs

Query_time: 11 Lock_time: 0 Rows_sent: 299925 Rows_examined: 983836

use rt3;
SELECT * FROM Transactions where Transactions.type = ‘create’;

Now looking at the explain I see

mysql> explain SELECT * FROM Transactions where Transactions.type = ‘create’;
±-------------±-----±--------------±-----±--------±-----±-------±------------+
| table | type | possible_keys | key | key_len | ref | rows
| Extra |
±-------------±-----±--------------±-----±--------±-----±-------±------------+
| Transactions | ALL | NULL | NULL | NULL | NULL | 984572
| Using where |
±-------------±-----±--------------±-----±--------±-----±-------±------------+

It has to go thru about 1 million rows and not using any index.

Is there any index, that I can create, to improve this w/o hurting any
other query?

–
Asif Iqbal
PGP Key: 0xE62693C5 KeyServer: pgp.mit.edu

Asif Iqbal
PGP Key: 0xE62693C5 KeyServer: pgp.mit.edu

6

This is mysql bug that wouldn’t be fixed in 4.1 branch, but is fixed
in 5.x. I know how to fix this on RT side, but it’s quite hard to
implement a solution and I don’t think we’ll have enough time for
this. Sorry. See also http://bugs.mysql.com/?id=20182On 10/24/06, Asif Iqbal vadud3@gmail.com wrote:

Here is another query that takes 6.95 secs

Count: 198 Time=6.95s (1377s) Lock=0.00s (0s) Rows=1.1 (224),
rt_user[rt_user]@localhost
SELECT DISTINCT main.* FROM (((Tickets main JOIN Groups Groups_1
ON ( Groups_1.Instance = main.id)) LEFT JOIN CachedGroupMembers
CachedGroupMembers_2 ON ( CachedGroupMembers_2.GroupId = Groups_1.id)
AND ( (CachedGroupMembers_2.GroupId !=
CachedGroupMembers_2.MemberId))) LEFT JOIN Users Users_3 ON (
Users_3.id = CachedGroupMembers_2.MemberId)) WHERE ((Groups_1.Domain
= ‘S’)) AND ((Groups_1.Type = ‘S’)) AND ((main.EffectiveId = main.id))
AND ((main.Status != ‘S’)) AND ((main.Type = ‘S’)) AND ( ( (
(Users_3.EmailAddress = ‘S’) ) ) AND ( (main.Status =
‘S’)OR(main.Status = ‘S’) ) ) ORDER BY main.Priority DESC LIMIT N

Is there a way to improve this?

On 10/9/06, Asif Iqbal vadud3@gmail.com wrote:

Hi

I am noticing from my slow log file (5 secs) that the following query
on mysql 4.0.24 is taking a 11 secs

Query_time: 11 Lock_time: 0 Rows_sent: 299925 Rows_examined: 983836

use rt3;
SELECT * FROM Transactions where Transactions.type = ‘create’;

Now looking at the explain I see

mysql> explain SELECT * FROM Transactions where Transactions.type = ‘create’;
±-------------±-----±--------------±-----±--------±-----±-------±------------+
| table | type | possible_keys | key | key_len | ref | rows
| Extra |
±-------------±-----±--------------±-----±--------±-----±-------±------------+
| Transactions | ALL | NULL | NULL | NULL | NULL | 984572
| Using where |
±-------------±-----±--------------±-----±--------±-----±-------±------------+

It has to go thru about 1 million rows and not using any index.

Is there any index, that I can create, to improve this w/o hurting any
other query?

–
Asif Iqbal
PGP Key: 0xE62693C5 KeyServer: pgp.mit.edu

–
Asif Iqbal
PGP Key: 0xE62693C5 KeyServer: pgp.mit.edu

The rt-users Archives

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.

7

This is mysql bug that wouldn’t be fixed in 4.1 branch, but is fixed
in 5.x. I know how to fix this on RT side, but it’s quite hard to
implement a solution and I don’t think we’ll have enough time for
this. Sorry. See also http://bugs.mysql.com/?id=20182

Currently I am using RT 3.4.5 with mysql Ver 12.22 Distrib 4.0.24,
for pc-solaris2.10 (i386)

Should I just upgrade the mysql? Is there a recommended way to upgrade
by RT community?

I have 169211 Tickets with 1006257 transactions total on a v20z with
4G real mem

Here is another query that takes 6.95 secs

Count: 198 Time=6.95s (1377s) Lock=0.00s (0s) Rows=1.1 (224),
rt_user[rt_user]@localhost
SELECT DISTINCT main.* FROM (((Tickets main JOIN Groups Groups_1
ON ( Groups_1.Instance = main.id)) LEFT JOIN CachedGroupMembers
CachedGroupMembers_2 ON ( CachedGroupMembers_2.GroupId = Groups_1.id)
AND ( (CachedGroupMembers_2.GroupId !=
CachedGroupMembers_2.MemberId))) LEFT JOIN Users Users_3 ON (
Users_3.id = CachedGroupMembers_2.MemberId)) WHERE ((Groups_1.Domain
= ‘S’)) AND ((Groups_1.Type = ‘S’)) AND ((main.EffectiveId = main.id))
AND ((main.Status != ‘S’)) AND ((main.Type = ‘S’)) AND ( ( (
(Users_3.EmailAddress = ‘S’) ) ) AND ( (main.Status =
‘S’)OR(main.Status = ‘S’) ) ) ORDER BY main.Priority DESC LIMIT N

Is there a way to improve this?

Hi

I am noticing from my slow log file (5 secs) that the following query
on mysql 4.0.24 is taking a 11 secs

Query_time: 11 Lock_time: 0 Rows_sent: 299925 Rows_examined: 983836

use rt3;
SELECT * FROM Transactions where Transactions.type = ‘create’;

Now looking at the explain I see

mysql> explain SELECT * FROM Transactions where Transactions.type = ‘create’;
±-------------±-----±--------------±-----±--------±-----±-------±------------+
| table | type | possible_keys | key | key_len | ref | rows
| Extra |
±-------------±-----±--------------±-----±--------±-----±-------±------------+
| Transactions | ALL | NULL | NULL | NULL | NULL | 984572
| Using where |
±-------------±-----±--------------±-----±--------±-----±-------±------------+

It has to go thru about 1 million rows and not using any index.

Is there any index, that I can create, to improve this w/o hurting any
other query?

–
Asif Iqbal
PGP Key: 0xE62693C5 KeyServer: pgp.mit.edu

–
Asif Iqbal
PGP Key: 0xE62693C5 KeyServer: pgp.mit.edu

The rt-users Archives

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.

Asif Iqbal
PGP Key: 0xE62693C5 KeyServer: pgp.mit.edu

8

Here is another query that takes 6.95 secs
…
AND ( (main.Status = > ‘S’)OR(main.Status = ‘S’) )
…
Is there a way to improve this?

Introducing an 'OR' into any SQL generally slows it down. In this case the OR appears to be unnecessary, so if there were a way to remove it this might make a difference.

Tony