[rt-users] Re: Slow Queries

[Asif Iqbal]

On 10/24/06, Ruslan Zakirov <ruslan.zakirov at gmail.com> wrote:
> 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


>
> On 10/24/06, Asif Iqbal <vadud3 at 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 at 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
> > _______________________________________________
> > http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
> >
> > Community help: http://wiki.bestpractical.com
> > Commercial support: sales at 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