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
On 10/24/06, Asif Iqbal <[EMAIL PROTECTED]> wrote:
Here is another query that takes 6.95 secs
Count: 198 Time=6.95s (1377s) Lock=0.00s (0s) Rows=1.1 (224),
[EMAIL PROTECTED]
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 <[EMAIL PROTECTED]> 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
_______________________________________________
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
Community help: http://wiki.bestpractical.com
Commercial support: [EMAIL PROTECTED]
Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
--
Best regards, Ruslan.
_______________________________________________
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
Community help: http://wiki.bestpractical.com
Commercial support: [EMAIL PROTECTED]
Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
