The following query has locked up the database for an hour.
What database?
Copying to tmp table | SELECT DISTINCT main.* FROM ( ( Tickets main LEFT
JOIN TicketCustomFieldValues as TicketCustomFieldValues_1 ON
((TicketCustomFieldValues_1.CustomField = ‘1’)) AND ( main.id =
TicketCustomFieldValues_1.Ticket)) LEFT JOIN TicketCustomFieldValues as
TicketCustomFieldValues_2 ON ((TicketCustomFieldValues_2.CustomField =
‘2’)) AND ( main.id = TicketCustomFieldValues_2.Ticket)) WHERE
((main.EffectiveId = main.id)) AND ((main.Type = ‘ticket’)) AND ( ( (
(TicketCustomFieldValues_1.Content LIKE ‘%%01Components%%’) ) OR (
(TicketCustomFieldValues_2.Content LIKE ‘%%023Store23%%’) ) ) AND (
(main.Status = ‘resolved’) ) ) ORDER BY main.id ASC LIMIT 50 |
Dean
rt-devel mailing list
rt-devel@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-devel
Request Tracker... So much more than a help desk — Best Practical Solutions – Trouble Ticketing. Free.
Using this split query, we get access times down to less than 300ms. I’ve attached the local/lib/RT/Users_Local.pm, local/lib/RT/Groups_Local.pm and local/html/Elements/SelectOwner that we are using, I’m curious if this method improves access times with other database engines.
I’ve finally had time to review this and test it against the test suite.
A slightly different version of this patch will be in the first 3.0.4
pre-release. Specifically, I’ve rewritten Users->WhoHaveRight to use the
new split routines rather than doing the “big” query, which means that
there are no changes needed for the SelectOwner component. I’ve also
cleaned things up a little bit and added a bit more flowthrough of
options. But it should be checked in in a matter of minutes.
Specifically, I rewrote Users->WhoAreInGroups to use the SearchBuilder
query building API, rather than to hand construct SQL queries. I note
that you were allowing it to find cases where the GroupId was NULL. Is
there a specific reason for this?
Thanks for the investigative work on this one and your patience while I
found time to check it out.
-j
Chris
Request Tracker... So much more than a help desk — Best Practical Solutions – Trouble Ticketing. Free.
I had the where GroupId = NULL in there just as a lazy way to ensure valid SQL even when the array of groups was undefined or empty. It seemed harmless enough, GroupId in GroupMembers is ‘NOT NULL’, although its not in CachedGroupMembers, it seemed unlikely this would ever cause a false match.
I wan’t sure if you’d want a subquery inside of Users->WhoHaveRight, so I just through this together assuming it would be revised before accepted.
Cheers
ChrisFrom: Jesse Vincent [mailto:jesse@bestpractical.com]
Sent: Monday, June 30, 2003 3:06 PM
To: Audley, Christopher
Cc: rt-devel@lists.fsck.com
Subject: Re: [rt-devel] Users::WhoHaveRight and Sybase
Using this split query, we get access times down to less than 300ms. I’ve attached the local/lib/RT/Users_Local.pm, local/lib/RT/Groups_Local.pm and local/html/Elements/SelectOwner that we are using, I’m curious if this method improves access times with other database engines.
I’ve finally had time to review this and test it against the test suite.
A slightly different version of this patch will be in the first 3.0.4
pre-release. Specifically, I’ve rewritten Users->WhoHaveRight to use the
new split routines rather than doing the “big” query, which means that
there are no changes needed for the SelectOwner component. I’ve also
cleaned things up a little bit and added a bit more flowthrough of
options. But it should be checked in in a matter of minutes.
Specifically, I rewrote Users->WhoAreInGroups to use the SearchBuilder
query building API, rather than to hand construct SQL queries. I note
that you were allowing it to find cases where the GroupId was NULL. Is
there a specific reason for this?
Thanks for the investigative work on this one and your patience while I
found time to check it out.
-j
Chris
Request Tracker... So much more than a help desk — Best Practical Solutions – Trouble Ticketing. Free.
I had the where GroupId = NULL in there just as a lazy way to ensure valid SQL even when the array of groups was undefined or empty. It seemed harmless enough, GroupId in GroupMembers is ‘NOT NULL’, although its not in CachedGroupMembers, it seemed unlikely this would ever cause a false match.
I wan’t sure if you’d want a subquery inside of Users->WhoHaveRight, so I just through this together assuming it would be revised before accepted.
Ah. Ok. I’m glad I wasn’t just missing something subtle. The Searchbuilderish
way to have it generate correct SQL is to use the Limit method inside the loop like this:
foreach my $groupid (@{$args{‘Groups’}}) {
$self->Limit(ALIAS => $cgm, FIELD => ‘GroupId’, VALUE => $groupid, QUOTE VALUE => 0, ENTRYAGGREGATOR=> ‘OR’)
}
Cheers
Request Tracker... So much more than a help desk — Best Practical Solutions – Trouble Ticketing. Free.
Dont know if this is of any help…but
What’s looking like a pattern is anything with a custom field really
chews up the db. The two following querries have been sitting in
the db for a while now:
2625 | Sending data | SELECT count(DISTINCT main.id) FROM ( Tickets main
LEFT JOIN TicketCustomFieldValues as TicketCustomFieldValues_1 ON
((TicketCustomFieldValues_1.CustomField = ‘2’)) AND ( main.id =
TicketCustomFieldValues_1.Ticket)) WHERE ((main.EffectiveId =
main.id)) AND ((main.Type = ‘ticket’)) AND ( ( (
(TicketCustomFieldValues_1.Content LIKE ‘%%005Store5%%’) ) ) )
| 72 | rt_user | localhost | rt3 | Query | 221 | Sending data |
SELECT count(DISTINCT main.id) FROM ( Tickets main LEFT JOIN
TicketCustomFieldValues as TicketCustomFieldValues_1 ON
((TicketCustomFieldValues_1.CustomField = ‘2’)) AND ( main.id =
TicketCustomFieldValues_1.Ticket)) WHERE ((main.EffectiveId = main.id))
AND ((main.Type = ‘ticket’)) AND ( ( (
(TicketCustomFieldValues_1.Content LIKE ‘%%022Store22%%’) ) ) AND (
(main.Queue = ‘17’) ) )
Dean
With rt 3.0.3 and MySQL 4.0.13, the query below runs for more than 10
minutes without returning. It is done when searching for more than one
requestor email address.
Anybody share some insight as how to speed this up?
Yep I hit this problem in the RT 2.0.x series, got around it by making it
so staff can ONLY search on one requestor at a time.
Can’t give you a patch against RT 3.x.x unforunately.
Regards
Matthew Watson
Netspace Online Systems
PH: (03)98110010