jackrabbit-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Christian Stocker <christian.stoc...@liip.ch>
Subject Re: Strange Search Performance problem with OR
Date Tue, 27 Mar 2012 10:01:39 GMT
Hi again

Here's another strange issue with query performance.

I have this query:

***
SELECT data.* FROM [own:unstructured] AS data LEFT OUTER JOIN
[nzz:unstructured] AS referring ON referring.reference = data.[jcr:uuid]
WHERE
        data.[phpcr:class] = 'Own\ApiBundle\Document\Article' AND
((data.publication = 'own')
        AND (CONTAINS(data.categories, 'KURZ'))
        AND (data.departmentSlugs = 'international'))
        AND ( ((data.permissionPath  NOT LIKE 'owns/%')))
        AND (data.title IS NOT NULL AND data.title <> '') AND
(data.teaser IS NOT NULL AND data.teaser <> '') AND (
        (referring.reference IS NOT NULL AND ISDESCENDANTNODE(referring,
'/article/2012/03/26'))
        OR ISDESCENDANTNODE(data, '/article/2012/03/26')
        ) ORDER BY data.modificationDate
***

This is fast enough when it finds something. When I change for example
data.departmentSlugs = 'international'
to something else, where it certainly isn't finding anything, it's damn
slow. 50ms vs. 2'500ms or so.

I'm sure there's an explenation for this :)

chregu





On 26.03.12 22:28, Christian Stocker wrote:
> Hi
> 
> We have the following search query
> 
> 
> SELECT * FROM [own:unstructured] AS data WHERE data.guid = 'J7B1X'
> 		AND (ISDESCENDANTNODE(data, '/article')
> 		OR ISDESCENDANTNODE(data, '/import/article')
> 		)
> 		ORDER BY firstImportDate DESC
> 
> 
> This query can take quite some time (up to 3 seconds, but it gets more
> and more hte more data we have). In /article there's potentially a lot
> of nodes, in /import/article usually almost nil.
> 
> 
> If we now separate the query into 2:
> 
> SELECT * FROM [own:unstructured] AS data WHERE data.guid = 'J7B1X'
> 		AND ISDESCENDANTNODE(data, '/article')
> 		ORDER BY firstImportDate DESC
> 
> and
> 
> SELECT * FROM [own:unstructured] AS data WHERE data.guid = 'J7B1X'
> 		AND ISDESCENDANTNODE(data, '/import/article')
> 		ORDER BY firstImportDate DESC
> 
> Both queries take approx. 10ms (and return 0 or 1 resultset, more is not
> possible). So quite fast.
> 
> Can anyone explain to me, why that is and how we could rewrite the query
> to make it fast with a single one as well?
> 
> Thanks
> 
> chregu

-- 
Liip AG  //  Feldstrasse 133 //  CH-8004 Zurich
Tel +41 43 500 39 81 // Mobile +41 76 561 88 60
www.liip.ch // blog.liip.ch // GnuPG 0x0748D5FE


Mime
View raw message