AW: Bad performance in a OR query

> Von: Michaël Fernandez wrote:

> I'm working on the usage of MaxDB in the RT System 
> (http://bestpractical.com/rt/).
> I'm using MaxDB 7.0.5.19.
> I obtain really bad performances on some queries.
> 
> This is the part of a query which is not well using indexes:
> SELECT main.id
> FROM Groups main, ACL ACL_1
> WHERE ((ACL_1.PrincipalId = main.id AND ACL_1.PrincipalType = 'Group')
> OR (main.Domain = 'RT::Queue-Role' AND main.Instance = 3  AND 
> main.Type 
> = ACL_1.PrincipalType))
> 
> This query is not using my indexes, the explain commands gives to me:
> '';'ACL_1';'';'TABLE SCAN';'         1'
> '';'MAIN';'';'TABLE SCAN';'       580'
> '';'';'';'     RESULT IS COPIED   , COSTVALUE IS';'    316698'
> 
> But the following query gives a similar result (if we don't concider 
> duplicates values):
> SELECT main.id
> FROM Groups main, ACL ACL_1
> WHERE ACL_1.PrincipalId = main.id AND ACL_1.PrincipalType = 'Group'
> UNION
> SELECT main.id
> FROM Groups main, ACL ACL_1
> WHERE main.Domain = 'RT::Queue-Role' AND main.Instance = 3  AND 
> main.Type = ACL_1.PrincipalType
> 
> and this query uses my indexes and is much faster. For this query the 
> execution plan is:
> '';'ACL_1';'';'TABLE SCAN';'         1'
> '';'MAIN';'ID';'JOIN VIA KEY COLUMN';'       580'
> '';'ACL_1';'';'TABLE SCAN';'         1'
> '';'MAIN';'GROUPS1';'JOIN VIA RANGE OF MULTIPLE INDEXED 
> COL.';'       580'
> '';'';'DOMAIN';'     (USED INDEX COLUMN)';''
> '';'';'INSTANCE';'     (USED INDEX COLUMN)';''
> '';'';'';'     RESULT IS COPIED   , COSTVALUE IS';'        19'
> 
> 
> The optimization is not well working when I'm using the query 
> with "OR".
> Notes that the real query is more complex. This is just a 
> part to show 
> that indexes are not used with combination of conditions with a "OR".
> 
> Is this possible to configure MaxDB to correctly use indexes 
> in the case ?

Hi,

no you couldn't change this behaviour by changing any configuration of MaxDB.

To evaluate the OR qualification in your example MaxDB first have to build
the cartesian product and checks the qualification against this result.

So the usage of unions seems to be the only way to speed up your query.

We are working on a query rewrite functonality that will do such changes 
from or joins to unions automatically but we are on the very beginning of 
this task so don't expect any results in the near futur.

Best regards
Holger
SAP Labs Berlin


--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe:    http://lists.mysql.com/maxdb?unsub=gcdm-maxdb@xxxxxxxxxxx