Slow queries on properties

[Michael Harrison <mi...@persistent.com>]

We are using Oak 1.6.1 to store a repository of digital assets, mostly images. Each asset is represented by a parent node of type nt:file that has properties and a child node of type nt:resource that holds the data bytes of the asset as a property. We want to search for assets based on the values of the parent node's properties. We are searching for nodes based on property values in two ways; either for a given property name or across all property names. We have defined an appropriate Lucene index and specified Lucene indexing to be done. We have verified that indexing is indeed being done and a suitable Lucene index is produced.


The following SQL-2 queries are slow:


SELECT [jcr:primaryType] FROM [nt:file] AS nodes WHERE nodes.* LIKE 'matchString'


SELECT [jcr:primaryType] FROM [nt:file] AS nodes WHERE nodes.[name] LIKE 'matchString'


I have read comments suggesting that the "LIKE" operator leads to slow searches. We have also tried using the CONTAINS function, for example:


SELECT [jcr:primaryType] FROM [nt:file] AS nodes WHERE CONTAINS(nodes.[name], 'matchString')


This syntax is accepted but returns no matching nodes.


We know that expressing the same query in Lucene syntax and searching the Lucene index directly results in a fast search. I presume that when Oak does a search it generates a Lucene query equivalent to the SQL-2 and executes that query via Lucene. Is it possible to see the generated Lucene query, for example by making an appropriate log setting?


Mike Harrison
DISCLAIMER
==========
This e-mail may contain privileged and confidential information which is the property of Persistent Systems Ltd. It is intended only for the use of the individual or entity to which it is addressed. If you are not the intended recipient, you are not authorized to read, retain, copy, print, distribute or use this message. If you have received this communication in error, please notify the sender and delete all copies of this message. Persistent Systems Ltd. does not accept any liability for virus infected mails.