[rt-users] DBIx::SearchBuilder oddity locking database

[Jeff Fearn]

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.
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: clean.sql
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20110920/477d44dd/attachment.ksh>
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: doom.sql
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20110920/477d44dd/attachment-0001.ksh>
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: rt-attr.txt
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20110920/477d44dd/attachment.txt>