[
http://jira.nuxeo.org/browse/NXP-4061?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=60132#action_60132
]
Florent Guillaume commented on NXP-4061:
----------------------------------------
For the example:
SELECT * FROM File
WHERE
dc:title = 'abc'
AND uid:uid = '123'
AND dc:contributors = 'bob' -- multi-valued
</pre>
If there are no proxies (ecm:isProxy = 0) we get:
SELECT hierarchy.id
FROM hierarchy
LEFT JOIN dublincore ON hierarchy.id = dublincore.id
LEFT JOIN uid ON hierarchy.id = uid.id
WHERE
hierarchy.primarytype IN ('File', 'SubFile')
AND dublincore.title = 'abc'
AND uid.uid = '123'
AND EXISTS (SELECT 1 FROM dc_contributors WHERE hierarchy.id =
dc_contributors.id
AND dc_contributors.item = 'bob')
AND NX_ACCESS_ALLOWED(hierarchy.id, 'user1|user2', 'perm1|perm2')
The data tables (dublincore, uid) are joined using a LEFT JOIN, as the schema
may not be present on all documents but this shouldn't prevent the WHERE
clause from being evaluated.
Complex properties are matched using an EXISTS and a subselect.
When proxies are matched (ecm:isProxy = 1) there are two additional FULL
JOINs. Security checks, id, name, parents and path use the base hierarchy
(_H), but all other data use the joined hierarchy.
SELECT _H.id
FROM hierarchy _H
JOIN proxies ON _H.id = proxies.id -- proxy full join
JOIN hierarchy ON hierarchy.id = proxies.targetid -- proxy full join
LEFT JOIN dublincore ON hierarchy.id = dublincore.id
LEFT JOIN uid ON hierarchy.id = uid.id
WHERE
hierarchy.primarytype IN ('File', 'SubFile')
AND dublincore.title = 'abc'
AND uid.uid = '123'
AND EXISTS (SELECT 1 FROM dc_contributors WHERE hierarchy.id =
dc_contributors.id
AND dc_contributors.item = 'bob')
AND NX_ACCESS_ALLOWED(_H.id, 'user1|user2', 'perm1|perm2') -- uses _H
When both normal documents and proxies are matched, we UNION ALL the two
queries. If an ORDER BY is requested, then columns from the inner SELECTs
have to be aliased so that an outer ORDER BY can user their names.
> VCS: improve proxy-based search performance
> -------------------------------------------
>
> Key: NXP-4061
> URL: http://jira.nuxeo.org/browse/NXP-4061
> Project: Nuxeo Enterprise Platform
> Issue Type: Bug
> Components: Core SQL Storage
> Affects Versions: 5.2 GA
> Reporter: Florent Guillaume
> Assignee: Florent Guillaume
> Priority: Critical
> Fix For: 5.2.1
>
> Original Estimate: 3 days
> Remaining Estimate: 3 days
>
> VCS searches involving proxies need to do a
> LEFT JOIN proxies ON proxies.id = _nxhier.id
> JOIN hierarchy ON (hierarchy.id = _nxhier.id OR hierarchy.id =
> proxies.targetid)
> and the OR part is very costly.
> Find a way to change this, either by using a UNION ALL of two queries (one
> one normal docs, the other on proxies), or something else.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://jira.nuxeo.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
_______________________________________________
ECM-tickets mailing list
[email protected]
http://lists.nuxeo.com/mailman/listinfo/ecm-tickets
