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 ?
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.