Slow Queries part3, Groups

Heya,

specs : rt 3.0.5 , mysql 4.0.15a-standard-log, freebsd 4.7

I’m about 3/4 of the way through my conversion from rt2-rt3. and noticing
that rt3
is a bit faster for certain things (searching by requestor). one very slow
query i’m
seeing is when looking for privileged users that a ticket may be assigned
to, this is what i’m
getting (apologies if this wraps abit weird)

mysql> explain SELECT DISTINCT main.* FROM Groups main, Principals
Principals_1, ACL ACL_2 WHERE ((ACL_2.RightName =
‘OwnTicket’)OR(ACL_2.RightName = ‘SuperUser’)) AND
→ ( ( ACL_2.PrincipalId = Principals_1.id
→ AND ACL_2.PrincipalType = ‘Group’
→ AND ( main.Domain = ‘SystemInternal’
→ OR main.Domain = ‘UserDefined’
→ OR main.Domain = ‘ACLEquivalence’)
→ AND main.id = Principals_1.id)
→ OR ( ( (main.Domain = ‘RT::Queue-Role’ AND main.Instance
= 13) OR ( main.Domain = ‘RT::Ticket-Role’ AND main.Instance = 363628) )
AND main.Type = ACL_2.PrincipalType AND main.id = Principals_1.id) )
→ AND (ACL_2.ObjectType = ‘RT::System’ OR (ACL_2.ObjectType
= ‘RT::Queue’ AND ACL_2.ObjectId = 13) ) ORDER BY main.Name ASC
→ ;
| table | type | possible_keys | key | key_len | ref
| rows | Extra |
| ACL_2 | range | ACL1 | ACL1 | 54 |
NULL | 21 | Using where; Using index; Using temporary; Using
filesort |
| main | ALL | PRIMARY,Groups1,Groups2 | NULL | NULL |
NULL | 1599452 | Using where
|
| Principals_1 | eq_ref | PRIMARY | PRIMARY | 4 |
main.id | 1 | Using where; Using index; Distinct
|
3 rows in set (0.00 sec)

I’m guessing a index om Groups.Domain will improve things a bit (currently
importing, so I don’t really want to generate an
index at the moment, as it will lock the tables).

Anyone else getting this?

Thanks

Matthew Watson
Netspace Online Systems.

I’m guessing a index om Groups.Domain will improve things a bit (currently
importing, so I don’t really want to generate an
index at the moment, as it will lock the tables).

Anyone else getting this?

Do you already have these two indexes?

CREATE INDEX Groups1 ON Groups (Domain,Instance,Type,id);
CREATE INDEX Groups2 On Groups (Type, Instance, Domain);

The first one should be applicable to the query you pasted.

What version of MySQL are you using?

-R

Yep, i’ve got those indexes,

Server version: 4.0.15a-standard-log

Regards
Matt Watson.–On Tuesday, October 07, 2003 10:31 PM -0700 Robert Spier rspier@pobox.com wrote:

I’m guessing a index om Groups.Domain will improve things a bit
(currently importing, so I don’t really want to generate an
index at the moment, as it will lock the tables).

Anyone else getting this?

Do you already have these two indexes?

CREATE INDEX Groups1 ON Groups (Domain,Instance,Type,id);
CREATE INDEX Groups2 On Groups (Type, Instance, Domain);

The first one should be applicable to the query you pasted.

What version of MySQL are you using?

-R


rt-devel mailing list
rt-devel@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-devel

At Wed, 08 Oct 2003 13:41:53 +0800,
Matthew Watson wrote:

Yep, i’ve got those indexes,

I wonder why Mysql isn’t picking them up. If you get a chance to
create the Domain index, or maybe (Domain,Instance) and run an EXPLAIN
on it,

Server version: 4.0.15a-standard-log

Hmm. Some older (4.0.12 era) MySQL had some issues with picking the
right index, but those were fixed.

The Groups1 index should be right for that query. I’m not sure why
it’s not getting picked up.

-R

I wonder why Mysql isn’t picking them up. If you get a chance to
create the Domain index, or maybe (Domain,Instance) and run an EXPLAIN
on it,

yep I tried that one, it doesn’t get picked up either :frowning:

Anyone else have ideas, this is really going to cause issues, as
it means when clicking on new ticket, it takes quite awhile (about 30-60
seconds) for the ticket window to load.

Regards
Matthew Watson,
Netspace Online Systems.

Anyone else have ideas, this is really going to cause issues, as
it means when clicking on new ticket, it takes quite awhile (about 30-60
seconds) for the ticket window to load.

Correction, the problem is actually when you click to reply to an existing
ticket.

well,

i’ve played around a little bit with this.

by dropping the search for the “RT::Queue-Role” or “RT::Ticket-Role” parts
of the query it index for primary kicks in (bringing the result down to
0.01 ms).

Now i’m not really sure what these fit in, I assume its for finding if a
Requestor can has that given right, perhaps this query would be better off
being broken down into two queries (I’ve spent about an hour trying to get
this query optimised, with no luck).

Any thoughts?

The query without the above bits looks like,

SELECT DISTINCT main.*
FROM Groups main, Principals Principals_1, ACL ACL_2
WHERE ((ACL_2.RightName = ‘OwnTicket’)OR(ACL_2.RightName = ‘SuperUser’))
AND ( ( ACL_2.PrincipalId = Principals_1.id
AND ACL_2.PrincipalType = ‘Group’
AND ( main.Domain = ‘SystemInternal’
OR main.Domain = ‘UserDefined’
OR main.Domain = ‘ACLEquivalence’)
AND main.id = Principals_1.id))
AND ( ACL_2.ObjectType = ‘RT::System’ OR ( ACL_2.ObjectType = ‘RT::Queue’
AND ACL_2.ObjectId = 36) )
ORDER BY main.Name ASC

Matt.

I wonder why Mysql isn’t picking them up. If you get a chance to
create the Domain index, or maybe (Domain,Instance) and run an EXPLAIN
on it,
yep I tried that one, it doesn’t get picked up either :frowning:
Anyone else have ideas, this is really going to cause issues, as
it means when clicking on new ticket, it takes quite awhile (about 30-60
seconds) for the ticket window to load.

Hey, I’m not done yet.

I’ve been reading the nitty-gritty parts of the MySQL documentation.

When I run the aforementioned query against my database (20k tickets,
10,000 users), it takes about a second. That’s not great, but when
the cache kicks in, it drops down to half a second. That’s still not
great.

Do you know what is triggering the query?

My next line of research is into MySQL’s IGNORE INDEX, FORCE INDEX,
and USE INDEX modifiers.

The one that seems most useful to us FORCE INDEX, which will change the table scan (type=ALL) to a type=range.

| table | type | possible_keys | key | key_len | ref | rows | Extra |
| ACL_2 | range | ACL1 | ACL1 | 54 | NULL | 12 | Using where; Using index; Using temporary; Using filesort |
| main | ALL | PRIMARY,Groups1,Groups2,Groups3,Groups4 | NULL | NULL | NULL | 93406 | Using where |
| Principals_1 | eq_ref | PRIMARY | PRIMARY | 4 | main.id | 1 | Using where; Using index; Distinct |

| table | type | possible_keys | key | key_len | ref | rows | Extra |
| ACL_2 | range | ACL1 | ACL1 | 54 | NULL | 12 | Using where; Using index; Using temporary; Using filesort |
| main | range | Groups3 | Groups3 | 65 | NULL | 53818 | Using where |
| Principals_1 | eq_ref | PRIMARY | PRIMARY | 4 | main.id | 1 | Using where; Using index; Distinct |

(So, it’s only got to check half the number of rows now… but thats still a lot.)

I’ve toyed with these two new indices, but neither seems to do much.

CREATE INDEX Groups3 ON Groups (Domain);
CREATE INDEX Groups4 ON Groups (Domain,id,Instance);

Basically speed doesn’t change much with any of the FORCE INDEX,
which means the indices aren’t right.

If I reduce the query to this:

explain SELECT DISTINCT main.* FROM Groups main, Principals
Principals_1, ACL ACL_2 WHERE ((ACL_2.RightName =
‘OwnTicket’)OR(ACL_2.RightName = ‘SuperUser’)) AND ( (
ACL_2.PrincipalId = Principals_1.id AND ACL_2.PrincipalType = ‘Group’
AND ( main.Domain = ‘ACLEquivalence’) AND main.id = Principals_1.id)
OR ( ( (main.Domain = ‘RT::Queue-Role’ AND main.Instance = 13) ) AND
main.Type = ACL_2.PrincipalType AND main.id = Principals_1.id) )

Then I’m down to 5483 rows and a range search, as opposed to 50000.
(Although that search still takes 0.41 seconds.) (And doesn’t produce
the right answer, obviously.)

I suspect the real problem here is MySQL’s optimizer is falling down.
Which means we may have to take an alternate track. Anyone want to
try MySQL 4.1? Or (Jesse?) maybe split the OR’s up into a few faster
queries? Another thought is to replace some of the text strings with lookup numbers… but that’ll slow other things down.

Ok. Enough time with this for tonight.

-R

See my other email (looks like we are on the same track).

When I run the aforementioned query against my database (20k tickets,
10,000 users), it takes about a second. That’s not great, but when
the cache kicks in, it drops down to half a second. That’s still not
great.

yeh, my real problem is I have almost 700,000 tickets and 150,000 users :slight_smile:
so that 1 second blows out a bit :slight_smile:

Do you know what is triggering the query?

Yep, its when you click “reply” on a ticket, its trying to find
people who have the right to own the ticket (so it also happens when
you click on “People”

I suspect the real problem here is MySQL’s optimizer is falling down.
Which means we may have to take an alternate track. Anyone want to
try MySQL 4.1? Or (Jesse?) maybe split the OR’s up into a few faster
queries? Another thought is to replace some of the text strings with
lookup numbers… but that’ll slow other things down.

Yep, there are a few places where mysql will refuse to use indexes,
I’d say probably in this case, its deciding that the index just
doesn’t cut the rows down enough and a sequential search will be faster.

I think probably a couple of faster queries will be better, unless
a mysql guru can optimise the current one?

Ok. Enough time with this for tonight.

Hehe, thanks for you efforts, its much appreciated.

Matt.

yeh, my real problem is I have almost 700,000 tickets and 150,000 users :slight_smile:
so that 1 second blows out a bit :slight_smile:

Oy.

Do you know what is triggering the query?

Yep, its when you click “reply” on a ticket, its trying to find
people who have the right to own the ticket (so it also happens when
you click on “People”

This was slow in RT2 as well. I solved it (er… “worked around”) by
making the “Owner” box a text field instead of a select box.

I suspect the real problem here is MySQL’s optimizer is falling down.
Which means we may have to take an alternate track. Anyone want to
try MySQL 4.1? Or (Jesse?) maybe split the OR’s up into a few faster
queries? Another thought is to replace some of the text strings with
lookup numbers… but that’ll slow other things down.

Yep, there are a few places where mysql will refuse to use indexes,
I’d say probably in this case, its deciding that the index just
doesn’t cut the rows down enough and a sequential search will be
faster.

Right. Thats what the documentation says. But even when I force
those indexes, it still doesn’t cut the rows down enough. (Although
if it maintains the 50% margin on your system… that might help.)

I think probably a couple of faster queries will be better, unless
a mysql guru can optimise the current one?

Refactoring this gets a little funny, because it may have to touch a
lot of code. The ugly query is built in Groups_Overlay.pm:WithRight
– and sets the Groups iterator to dtrt. (And called from
Users_Overlay.pm:WhoHaveRight).

(Jesse-
1- Why doesn’t this use a recursive search and therefore use
CachedGroupMembers?
2- IncludeSystemRights doesn’t seem to do anything anymore.
Should it be excised?
)

Ok. Enough time with this for tonight.
Hehe, thanks for you efforts, its much appreciated.

My pleasure. While your database is huge, we do want to try and nip
these issues.

-R

Hello. Here is my comments on this.

Robert Spier wrote:

yeh, my real problem is I have almost 700,000 tickets and 150,000 users :slight_smile:
so that 1 second blows out a bit :slight_smile:

Oy.
Really HUGE.

[snip]

I suspect the real problem here is MySQL’s optimizer is falling down.
Which means we may have to take an alternate track. Anyone want to
try MySQL 4.1? Or (Jesse?) maybe split the OR’s up into a few faster
queries? Another thought is to replace some of the text strings with
lookup numbers… but that’ll slow other things down.

Yep, there are a few places where mysql will refuse to use indexes,
I’d say probably in this case, its deciding that the index just
doesn’t cut the rows down enough and a sequential search will be
faster.

Right. Thats what the documentation says. But even when I force
those indexes, it still doesn’t cut the rows down enough. (Although
if it maintains the 50% margin on your system… that might help.)
At first time I was thinking that this query is exactly that situation
when mySQL optimizer break plan, but for my DB mySQL it’s best plan with
existent indexes.
I’ve used ‘STRAIGHT_JOIN’ pragma and play with joins order and default
plan was the best by ‘query time’.

Matthew, could you test next query on your DB and compare time:
SELECT STRAIGHT_JOIN DISTINCT main.*
FROM
ACL ACL_2,
Principals Principals_1,
Groups main
WHERE … ORDER BY main.Name ASC;

This select uses indexes in all joins, but for me it’s ~3 times slower
than native plan with sequential search on Groups table.

[snip]

(Jesse-
1- Why doesn’t this use a recursive search and therefore use
CachedGroupMembers?
2- IncludeSystemRights doesn’t seem to do anything anymore.
Should it be excised?
)
I think that query is broken or do more then it has to:
SELECT DISTINCT main.*
FROM Groups main,
Principals Principals_1,
ACL ACL_2
WHERE (
(ACL_2.RightName = ‘OwnTicket’) OR
(ACL_2.RightName = ‘SuperUser’)
) AND (
(
ACL_2.PrincipalId = Principals_1.id AND
ACL_2.PrincipalType = ‘Group’ AND
(
main.Domain = ‘SystemInternal’ OR
main.Domain = ‘UserDefined’ OR
main.Domain = ‘ACLEquivalence’
) AND
main.id = Principals_1.id
) OR (
(
(
main.Domain = ‘RT::Queue-Role’ AND
main.Instance = 13
) OR (
main.Domain = ‘RT::Ticket-Role’ AND
main.Instance = 363628
)
) AND
main.Type = ACL_2.PrincipalType AND
main.id = Principals_1.id
)
) AND (
ACL_2.ObjectType = ‘RT::System’ OR
(
ACL_2.ObjectType = ‘RT::Queue’ AND
ACL_2.ObjectId = 13
)
) ORDER BY main.Name ASC;
I don’t understand why we need Principals table in it?

If I eliminate Principals_1.id at all and also ‘select from’ and
‘ACL_2.PrincipalId = Principals_1.id’ change to ‘ACL_2.PrincipalId =
main.id’ then I have same results, but query is faster.

Has anyone successfully implemented RT 3.x with FastCGI and Apache 1.3.x,
MySQL 3.x and Perl 5.8.0?

If so, could you please provide a sample httpd conf for this configuration?

Also, has anyone implemented multiple virtual hosts on the same server box
using RT and FastCGI?

Thanks in advance,

Dan

Notes:
The FastCcgiServer line and the three lines below it will need to be one
line in your httpd.conf. I split them up here to make it easier to read.

The -initial-env parameter is only needed if you use RH9 and haven’t
forced LANG to a non UTF8 value though other means.

You must have perlsuid installed otherwise FCGI-RT will NOT work.

Matt

DocumentRoot "/opt/vws/webapp1/rt/htdocs" ServerName your.server.name.here ServerAdmin webmaster@your.server.name.here ErrorLog /opt/vws/webapp1/rt/logs/error_log TransferLog /opt/vws/webapp1/rt/logs/access_log

AddDefaultCharset UTF-8
AddHandler fastcgi-script fcgi
FastCgiServer /opt/vws/webapp1/rt/bin/mason_handler.fcgi
-init-start-delay 15
-processes 2
-initial-env LANG=en_US

ScriptAlias / /opt/vws/webapp1/rt/bin/mason_handler.fcgi/

Standard SSL Cruft

.

.

.

  1. Your question is more appropriate for rt-users, not rt-devel.
  2. Please don’t randomly reply to messages to start new threads.

Has anyone successfully implemented RT 3.x with FastCGI and Apache 1.3.x,
MySQL 3.x and Perl 5.8.0?

MySQL 3.x is not recommended. MySQL 4 will perform much better.

Matthew, could you test next query on your DB and compare time:
SELECT STRAIGHT_JOIN DISTINCT main.*
FROM
ACL ACL_2,
Principals Principals_1,
Groups main
WHERE … ORDER BY main.Name ASC;

This select uses indexes in all joins, but for me it’s ~3 times slower
than native plan with sequential search on Groups table.

This seemed to cut about a minute off the query (from 2.5 to 1.5 minutes),
although its possible my db was very busy.

If I eliminate Principals_1.id at all and also ‘select from’ and
‘ACL_2.PrincipalId = Principals_1.id’ change to ‘ACL_2.PrincipalId =
main.id’ then I have same results, but query is faster.

hrmm, not sure, jesse?

Regards,
Matthew Watson.

Since I obviously can’t leave well-enough alone, I futzed a little
more tonight.

By splitting the query into two pieces, I can make MySQL’s optimizer
much happier.

SELECT DISTINCT main.* FROM Groups main, Principals Principals_1, ACL
ACL_2 WHERE ( ( ACL_2.RightName = ‘SuperUser’ OR ACL_2.RightName =
‘OwnTicket’ ) AND ( ACL_2.PrincipalId = Principals_1.id AND
ACL_2.PrincipalType = ‘Group’ AND ( main.Domain = ‘SystemInternal’ OR
main.Domain = ‘UserDefined’ OR main.Domain = ‘ACLEquivalence’ ) AND
main.id = Principals_1.id) AND ( ACL_2.ObjectType = ‘RT::System’ OR (
ACL_2.ObjectType = ‘RT::Queue’ AND ACL_2.ObjectId = 13 ) )
)
UNION
SELECT DISTINCT main.* FROM Groups main, Principals
Principals_1, ACL ACL_2 WHERE ( ( ACL_2.RightName = ‘SuperUser’ OR
ACL_2.RightName = ‘OwnTicket’ ) AND ( ( ( main.Domain =
‘RT::Queue-Role’ AND main.Instance = 13 ) OR ( main.Domain =
‘RT::Ticket-Role’ AND main.Instance = 363628 ) ) AND main.Type =
ACL_2.PrincipalType AND main.id = Principals_1.id ) AND (
ACL_2.ObjectType = ‘RT::System’ OR ( ACL_2.ObjectType = ‘RT::Queue’
AND ACL_2.ObjectId = 13 ) ) );

Adding this index makes the second half even happier:
CREATE INDEX Groups9 ON Groups (Type,Domain,Instance);

(The first half deals with global and group rights. The second half
deals with individual ticket (“role”) rights, like AdminCC and Owner.

The fun part:
1- UNION is not standardized between databases.
2- SearchBuilder doesn’t know about UNION
3- The code that builds this query is already a mess of special
cases and “cheating”.

My current possible solution is to add some sort of ‘preload’ to
RT::SearchBuilder (or just RT::Groups) so you can build a search and
get all the values for the iterator preloaded… then Groups::WithRight
would just (simply) perform two searches and set the iterator values
to the results of both. The question, of course, is what would that
break.

Another solution, which might be cheating, is to add another argument
to WithRight – IncludeRoleRights, default to 1. (Going back to the
original reason here of building a list of possible owners) While
“Owner” implies certain rights, the list of possible owners comes from
the Queue, so there’s no reason to even perform the Role search
(second half above). You then test for IncludeRoleRights on the line
( if ( defined $args{‘Object’} ) { ) that guards the building of the
RoleRight part of the query.

A little wave of the magic wand, and simple searches run faster. tada!

-R

(Note, the maximum value in the ‘rows’ column is now 869… much different than the 50 or 90k we had before.)

| table | type | possible_keys | key | key_len | ref | rows | Extra |
| ACL_2 | range | ACL1 | ACL1 | 79 | NULL | 12 | Using where; Using index; Using temporary |
| Principals_1 | eq_ref | PRIMARY | PRIMARY | 4 | ACL_2.PrincipalId | 1 | Using index |
| main | eq_ref | PRIMARY,Groups1,Groups3,Groups4 | PRIMARY | 4 | Principals_1.id | 1 | Using where |
| ACL_2 | range | ACL1 | ACL1 | 54 | NULL | 12 | Using where; Using index; Using temporary |
| main | ref | PRIMARY,Groups1,Groups2,Groups8,Groups9,Groups3,Groups4 | Groups9 | 65 | ACL_2.PrincipalType | 869 | Using where |
| Principals_1 | eq_ref | PRIMARY | PRIMARY | 4 | main.id | 1 | Using index; Distinct |

Hello. Again.
Robert Spier wrote:

Since I obviously can’t leave well-enough alone, I futzed a little
more tonight.

By splitting the query into two pieces, I can make MySQL’s optimizer
much happier.

SELECT DISTINCT main.* FROM Groups main, Principals Principals_1, ACL
ACL_2 WHERE ( ( ACL_2.RightName = ‘SuperUser’ OR ACL_2.RightName =
‘OwnTicket’ ) AND ( ACL_2.PrincipalId = Principals_1.id AND
ACL_2.PrincipalType = ‘Group’ AND ( main.Domain = ‘SystemInternal’ OR
main.Domain = ‘UserDefined’ OR main.Domain = ‘ACLEquivalence’ ) AND
main.id = Principals_1.id) AND ( ACL_2.ObjectType = ‘RT::System’ OR (
ACL_2.ObjectType = ‘RT::Queue’ AND ACL_2.ObjectId = 13 ) )
)
I’m not best of best in DBs, but I want draw your attention to table
Principals and where clauses of this select.
First of all, we use only id from this table.
Second, in accordance with RT::Group->PrincipalObj

$self->{‘PrincipalObj’} = RT::Principal->new($self->CurrentUser);
$self->{‘PrincipalObj’}->LoadByCols(‘ObjectId’ => $self->Id,
‘PrincipalType’ => ‘Group’) ;

So for this still working only because of this:
select count() from Principals where id != ObjectId;
| count(
) |
| 0 |
And at last, query MUST be:
SELECT DISTINCT main.*
FROM
Groups main,
Principals Principals_1,
ACL ACL_2
WHERE (
(
ACL_2.RightName = ‘SuperUser’ OR
ACL_2.RightName = ‘OwnTicket’
) AND (
ACL_2.PrincipalId = Principals_1.id AND
ACL_2.PrincipalType = ‘Group’ AND
(
main.Domain = ‘SystemInternal’ OR
main.Domain = ‘UserDefined’ OR
main.Domain = ‘ACLEquivalence’
) AND
main.id = Principals_1.ObjectId AND
Principals_1.PrincipalType = ‘Group’
) AND (
ACL_2.ObjectType = ‘RT::System’ OR
(
ACL_2.ObjectType = ‘RT::Queue’ AND
ACL_2.ObjectId = 8
)
)
)

UNION
SELECT DISTINCT main.* FROM Groups main, Principals
Principals_1, ACL ACL_2 WHERE ( ( ACL_2.RightName = ‘SuperUser’ OR
ACL_2.RightName = ‘OwnTicket’ ) AND ( ( ( main.Domain =
‘RT::Queue-Role’ AND main.Instance = 13 ) OR ( main.Domain =
‘RT::Ticket-Role’ AND main.Instance = 363628 ) ) AND main.Type =
ACL_2.PrincipalType AND main.id = Principals_1.id ) AND (
ACL_2.ObjectType = ‘RT::System’ OR ( ACL_2.ObjectType = ‘RT::Queue’
AND ACL_2.ObjectId = 13 ) ) );

[snip]

Best regards. Ruslan.

I’m not best of best in DBs, but I want draw your attention to table
Principals and where clauses of this select.
First of all, we use only id from this table.
Second, in accordance with RT::Group->PrincipalObj

I don’t believe the Principal’s stuff is the slow part of this query.
(At least not from my analysis of the EXPLAIN output.) I know the
Principal stuff is there for a reason, I believe it has to do with
delegation of rights, but Jesse can explain that better.

And at last, query MUST be:
SELECT DISTINCT main.*

What did I screw up?

-R

Robert Spier wrote:

I’m not best of best in DBs, but I want draw your attention to table
Principals and where clauses of this select.
First of all, we use only id from this table.
Second, in accordance with RT::Group->PrincipalObj

I don’t believe the Principal’s stuff is the slow part of this query.
(At least not from my analysis of the EXPLAIN output.) I know the
Principal stuff is there for a reason, I believe it has to do with
delegation of rights, but Jesse can explain that better.
Ugh.
I talk not about perfomance issues. I talk about query itself. It’s
wrong and only one particular feature save RT from logic breakage of
results from this select. This feature is that that each row in
Principals table have ObjectId == id.
Groups.id (in our select main.id) referenced not by Principals.id, but
by Principals.ObjectId.

Open your eyes and look at the query logic and not on how it’s fast or no.

Best regards and nothing personal. Ruslan.

Ruslan U. Zakirov wrote:

Robert Spier wrote:

I’m not best of best in DBs, but I want draw your attention to table
Principals and where clauses of this select.
First of all, we use only id from this table.
Second, in accordance with RT::Group->PrincipalObj

I don’t believe the Principal’s stuff is the slow part of this query.
(At least not from my analysis of the EXPLAIN output.) I know the
Principal stuff is there for a reason, I believe it has to do with
delegation of rights, but Jesse can explain that better.

Ugh.
I talk not about perfomance issues. I talk about query itself. It’s
wrong and only one particular feature save RT from logic breakage of
results from this select. This feature is that that each row in
Principals table have ObjectId == id.
Groups.id (in our select main.id) referenced not by Principals.id, but
by Principals.ObjectId.

Open your eyes and look at the query logic and not on how it’s fast or no.

Best regards and nothing personal. Ruslan.

And at last, query MUST be:
SELECT DISTINCT main.*

What did I screw up?
You - nothing. It’s Jesse :slight_smile: as I think.

-R

Also second part as I think have to be something like this:
SELECT DISTINCT main.*
FROM
Groups main,
ACL ACL_2
WHERE (
(
ACL_2.RightName = ‘SuperUser’ OR
ACL_2.RightName = ‘OwnTicket’
) AND (
(
(
main.Domain = ‘RT::Queue-Role’ AND
main.Instance = 8
) OR (
main.Domain =
‘RT::Ticket-Role’ AND
main.Instance = 12000
)
) AND
main.Type = ACL_2.PrincipalType
) AND (
ACL_2.ObjectType = ‘RT::System’ OR
(
ACL_2.ObjectType = ‘RT::Queue’ AND
ACL_2.ObjectId = 8
)
)
);