Hi, using RT 4.0.2 and DBIx::SearchBuilder 1.61 (also tested 1.59) I can
reliably create a search using the RT4 GUI that locks up the database.
It appears that the search used to generate the 'Found X tickets' text
generates a large set of joins which murders MySQL :(
The query is a simple search with a created-date and 9
Requestor.EmailAddress' to check. See attached rt-attr.txt. Note that
the dataset the query returns is only 72 tickets.
The count query generated for this has 10 full joins and 9 left joins.
See attached doom.sql, which I have manually formatted for easier
reading. It was generated by SearchBuilder 1.61; explain shows it
hitting 43K rows. SearchBuilder 1.59 is even more inefficient and is
hitting 53K rows. It has more joins and less left joins, but about the
same number in total.
Attached is what I believe is an equivalent count statement (clean.sql)
which hits 9 rows.
The original query was created in RT3 and according to the user didn't
have any problem running. Certainly any effect was limited enough that
none of the sys-admins noticed it running. In RT4 however, with much
newer SearchBuilder, it gets a lot of table locks in thee DB and
prevents anyone from doing anything, which leads to 500 errors.
Any help is much appreciated.
Cheers, Jeff.
explain SELECT COUNT(DISTINCT main.id) FROM Tickets main
JOIN Groups Groups_1 ON ( Groups_1.Domain = 'RT::Ticket-Role' ) AND (
Groups_1.Type = 'Requestor' ) AND ( Groups_1.Instance = main.id )
JOIN CachedGroupMembers CachedGroupMembers_18 ON
(CachedGroupMembers_18.GroupId = Groups_1.id )
LEFT JOIN Users Users_3 ON ( Users_3.id = CachedGroupMembers_18.MemberId )
WHERE
( main.Status != 'deleted' )
AND (
main.Created > '2010-08-31 14:00:00'
AND (
( Users_3.EmailAddress = 'us...@redhat.com' )
OR ( Users_3.EmailAddress = 'us...@redhat.com' )
OR ( Users_3.EmailAddress = 'us...@redhat.com' )
OR ( Users_3.EmailAddress = 'us...@redhat.com' )
OR ( Users_3.EmailAddress = 'us...@redhat.com' )
OR ( Users_3.EmailAddress = 'us...@redhat.com' )
OR ( Users_3.EmailAddress = 'us...@redhat.com' )
OR ( Users_3.EmailAddress = 'us...@redhat.com' )
OR ( Users_3.EmailAddress = 'us...@redhat.com' )
)
)
AND (main.Type = 'ticket')
AND (main.EffectiveId = main.id)
;
explain SELECT COUNT(DISTINCT main.id) FROM Tickets main
JOIN Groups Groups_1 ON ( Groups_1.Domain = 'RT::Ticket-Role' ) AND (
Groups_1.Type = 'Requestor' ) AND ( Groups_1.Instance = main.id )
JOIN CachedGroupMembers CachedGroupMembers_18 ON
(CachedGroupMembers_18.GroupId = Groups_1.id )
JOIN CachedGroupMembers CachedGroupMembers_8 ON (
CachedGroupMembers_8.GroupId = Groups_1.id )
JOIN CachedGroupMembers CachedGroupMembers_16 ON (
CachedGroupMembers_16.GroupId = Groups_1.id )
JOIN CachedGroupMembers CachedGroupMembers_12 ON (
CachedGroupMembers_12.GroupId = Groups_1.id )
JOIN CachedGroupMembers CachedGroupMembers_2 ON
(CachedGroupMembers_2.GroupId = Groups_1.id )
JOIN CachedGroupMembers CachedGroupMembers_6 ON (
CachedGroupMembers_6.GroupId = Groups_1.id )
JOIN CachedGroupMembers CachedGroupMembers_4 ON (
CachedGroupMembers_4.GroupId = Groups_1.id )
JOIN CachedGroupMembers CachedGroupMembers_14 ON (
CachedGroupMembers_14.GroupId = Groups_1.id )
JOIN CachedGroupMembers CachedGroupMembers_10 ON (
CachedGroupMembers_10.GroupId = Groups_1.id )
LEFT JOIN Users Users_15 ON ( Users_15.id = CachedGroupMembers_14.MemberId )
LEFT JOIN Users Users_7 ON ( Users_7.id = CachedGroupMembers_6.MemberId )
LEFT JOIN Users Users_17 ON ( Users_17.id = CachedGroupMembers_16.MemberId )
LEFT JOIN Users Users_5 ON ( Users_5.id = CachedGroupMembers_4.MemberId )
LEFT JOIN Users Users_3 ON ( Users_3.id = CachedGroupMembers_2.MemberId )
LEFT JOIN Users Users_13 ON ( Users_13.id = CachedGroupMembers_12.MemberId )
LEFT JOIN Users Users_19 ON ( Users_19.id = CachedGroupMembers_18.MemberId )
LEFT JOIN Users Users_9 ON ( Users_9.id = CachedGroupMembers_8.MemberId )
LEFT JOIN Users Users_11 ON ( Users_11.id = CachedGroupMembers_10.MemberId )
WHERE
( main.Status != 'deleted' )
AND (
main.Created > '2010-08-31 14:00:00'
AND (
( Users_3.EmailAddress = 'us...@redhat.com' )
OR ( Users_5.EmailAddress = 'us...@redhat.com' )
OR ( Users_7.EmailAddress = 'us...@redhat.com' )
OR ( Users_9.EmailAddress = 'us...@redhat.com' )
OR ( Users_11.EmailAddress = 'us...@redhat.com' )
OR ( Users_13.EmailAddress = 'us...@redhat.com' )
OR ( Users_15.EmailAddress = 'us...@redhat.com' )
OR ( Users_17.EmailAddress = 'us...@redhat.com' )
OR ( Users_19.EmailAddress = 'us...@redhat.com' )
)
)
AND (main.Type = 'ticket')
AND (main.EffectiveId = main.id)
;
$ rt-attributes-viewer 15787
Content of attribute #15787: $VAR1 = {
'ContentType' => 'storable',
'Content' => {
'Order' => 'ASC|ASC|ASC|ASC',
'Query' => 'Created > \'2010-09-01\' AND (
Requestor.EmailAddress = \'us...@example.com\' OR Requestor.EmailAddress =
\'us...@example.com\' OR Requestor.EmailAddress = \'us...@example.com\' OR
Requestor.EmailAddress = \'us...@example.com\' OR Requestor.EmailAddress =
\'us...@example.com\' OR Requestor.EmailAddress = \'us...@example.com\' OR
Requestor.EmailAddress = \'us...@example.com\' OR Requestor.EmailAddress =
\'us...@example.com\' OR Requestor.EmailAddress = \'us...@example.com\' )',
'SearchType' => 'Ticket',
'OrderBy' => 'id|||',
'Format' => '\' <b><a
href="__WebPath__/Ticket/Display.html?id=__id__">__id__</a></b>/TITLE:#\',
\'<b><a
href="__WebPath__/Ticket/Display.html?id=__id__">__Subject__</a></b>/TITLE:Subject\',
\'__Status__\',
\'__QueueName__\',
\'__OwnerName__\',
\'__Priority__\',
\'__NEWLINE__\',
\'\',
\'<small>__Requestors__</small>\',
\'<small>__CreatedRelative__</small>\',
\'<small>__ToldRelative__</small>\',
\'<small>__LastUpdatedRelative__</small>\',
\'<small>__TimeLeft__</small>\'',
'RowsPerPage' => '50'
},
'Description' => 'Test evil',
'Name' => 'SavedSearch',
'ObjectId' => '1235',
'ObjectType' => 'RT::User'
};
--------
RT Training Sessions (http://bestpractical.com/services/training.html)
* Chicago, IL, USA September 26 & 27, 2011
* San Francisco, CA, USA October 18 & 19, 2011
* Washington DC, USA October 31 & November 1, 2011
* Melbourne VIC, Australia November 28 & 29, 2011
* Barcelona, Spain November 28 & 29, 2011
