[rt-users] Inefficient query generation? (RT 3.2.2)

[Kumar, Vasanth]

Jesse,

I tried RT 3.2.3rc1 and also tied SearchBuilder 1.16 with the same
results.  RT seems to generate the same SQL query.

The thing that seems to be really slowing down this query is the OR.  If
you were to generate a nested query I think this can be much faster.
Unfortunately only MySQL 4.1 supports subqueries.

Not that this is not the only inefficient query.  I have seen it also
generate bad queries in situations where I do something like: 

	(Queue = "QueueA" OR Queue = "QueueB") 
	AND (CF.QueueA.{FieldX} = '...' OR CF.QueueB.{FieldY} = '...')

I realize that some of these queries are inherently complex, but do you
have any plans for dealing with users putting in overly complex queries?
Maybe building in checks to prevent such complex queries from being run?

Since the search capabilities much more advanced in RT 3.2, we now have
to worry about our RT users creating these kind of complex queries than
can hang our databases.

Do you have any suggestions on how to deal with this problem or how
other users of RT deal with this issue?

Thank you.

Vasanth

-----Original Message-----
From: Jesse Vincent [mailto:jesse at bestpractical.com] 
Sent: Wednesday, December 15, 2004 7:30 PM
To: Kumar, Vasanth
Cc: rt-users at lists.bestpractical.com
Subject: Re: [rt-users] Inefficient query generation? (RT 3.2.2)



On Wed, Dec 15, 2004 at 07:16:05PM -0500, Kumar, Vasanth wrote:
> Hi,
> 
> I was trying out a seemingly simple query - I just wanted to view a 
> ticket and all tickets that depend on it.  So I created a query in RT
> (3.2.2):
> 
> 	DependentOn = '813'  OR id = '813'
> 
Give 3.2.3rc1 a shot? Also be sure you're using the latest
searchbuilder. I know we've done a bunch of work on optimizing a bunch
of the joins recently.

 

-----Original Message-----
From: Kumar, Vasanth 
Sent: Wednesday, December 15, 2004 7:16 PM
To: rt-users at lists.bestpractical.com
Subject: Inefficient query generation? (RT 3.2.2)

Hi,

I was trying out a seemingly simple query - I just wanted to view a
ticket and all tickets that depend on it.  So I created a query in RT
(3.2.2):

	DependentOn = '813'  OR id = '813'

However, this hung the DB (MySQL 4.0.15) with the SQL query:
(apparently never returns - I have to kill the select process)
	
	SELECT COUNT(DISTINCT main.id) 
	FROM Tickets main , Links Links_1  
	WHERE ((main.EffectiveId = main.id)) 
	AND ((main.Status != 'deleted')) 
	AND ( ( (Links_1.Type = 'DependsOn')
	AND(Links_1.LocalBase = '813')
	AND(main.id = Links_1.LocalTarget) ) 
	OR(main.id = '813'))

The OR appears to cause a full table scan through the cross-product of
both databases.  In my database, the Ticket has about 15,000 rows and
Links has about 25000 rows.  I tried creating numerous indices on Links
but to no avail.

Is there a set up indices that I can create that can speed up this
query?

Does RT create more intelligent SQL in future versions?

Thanks!