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
