Slow NXQL queries

I have some NXQL queries that are pretty slow. Using this https://doc.nuxeo.com/nxdoc/monitoring-slow-nxql-queries/ I was able to get some of the slowest NXQL queries This one took 870 ms:

QueryFilter(principal=myOrganisation, limit=0, offset=0) count total results UNLIMITED 
query SELECT * FROM Document 
WHERE (myOrganisation.common:unpublished = 0) 
    AND (ecm:isVersion = 0) 
    AND (myOrganisation.common:removed = 0) 
    AND (dc:source = 'MyOrganisation') 
    AND (myOrganisation.common:migrating = 0) 
    AND (ecm:path STARTSWITH '/default-domain/workspaces/MyOrganisation/organisations/1011/legal' 
        OR ecm:path STARTSWITH '/default-domain/workspaces/MyOrganisation/organisations/1011/business' 
        OR ecm:path STARTSWITH '/default-domain/workspaces/MyOrganisation/organisations/1011/projects/5180')

I notice that the more clauses ecm:path STARTSWITH '/default-domain/workspaces... are present, the slowest is the query. An other similar request with approximately 40 ecm:path STARTSWITH ... clauses took 3200 ms.

Is it a bad practice to write NXQL queries like this ?

0 votes

1 answers

96 views

ANSWER



Yes it's bad practice because STARTSWITH queries are inherently costly (as you can imagine). You should review your data modeling to make it more amenable to efficient queries.

0 votes