[Java] [1.7-rc2] Issues with Class Inheritance and Automatic Indices

[Justin Harris]

I'm having some issues with using automatic indices.  Here's what I have:

V

^

|

English (has "label" property)

^

|

Food

I'm doing bulk inserts so made the vertex types first, then inserted data, as recommended.

I inserted the data into Food.

I want to index everything in English by its label property.  So I made an index on English:

_g.command(new OCommandSQL("CREATE INDEX IX_English_label ON English (label) FULLTEXT")).execute();

(I had trouble getting case insensitive queries working with the Java API so I used that command to make the index)

Here's a query with the index:

explain SELECT * FROM index:IX_English_label WHERE key = 'apple'

@version	elapsed	resultType	resultSize
0	0.201853	collection	49

It's pretty fast and there were 49 results found.

Here's a query where on the English table where I would have thought that the index would be used automatically, it's much slower.

explain SELECT * FROM English WHERE label = 'apple'

@version	involvedIndexes	current	fetchingFromTargetElapsed	documentReads	documentAnalyzedCompatibleClass	recordReads	elapsed	resultType	resultSize
0	[1]	#12:117790	2307	117791	117791	117791	2307.6055	collection	2

explain SELECT * FROM Food WHERE label = 'apple'

@version	involvedIndexes	current	fetchingFromTargetElapsed	documentReads	documentAnalyzedCompatibleClass	recordReads	elapsed	resultType	resultSize
0	[1]	#12:117790	2291	117791	117791	117791	2292.1934	collection	2

So querying the table knew that there were indices involved but the query looks like it still traversed the entire table.

Why doesn't querying the table take advantage of the automatic index?

BTW I'm a little concerned with the 49 vs. 2 results, if the index was really used, then why are there only 2 results?

Thanks.

[Artem Orobets]

Hi Justin,

SELECT * FROM index:IX_English_label WHERE key = 'apple'

SELECT * FROM English WHERE label = 'apple'

The queries are not equivalent. The first one looks for record that contain word 'apple' in label field. The second one looks for records with label field equal to 'apple'.

That's why the second query can't be optimized to use index.

And that is why result count is different, you have 49 records that contain 'apple' in label, but you have only 2 records whose label is exactly 'apple'

Best regards,

Artem Orobets

Orient Technologies

the Company behind OrientDB

--

---
You received this message because you are subscribed to the Google Groups "OrientDB" group.
To unsubscribe from this group and stop receiving emails from it, send an email to orient-databa...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Justin Harris]

Thanks.  Fair enough.  Any insight then on how to get the automatic index working on the English table?

Also how would I use the index to find records that only match "apple", I tried looking at the docs but I can't get it to work.

(I'm using a FULLTEXT index with SBTREE on the English table on the label property)

I tried using regex:

SELECT * FROM index:IX_English_label WHERE key MATCHES "^apple$"

but that returned nothing.

Maybe it's related:

SELECT * FROM index:IX_English_label WHERE key LIKE "%apple"

returns no results too but 

SELECT * FROM en WHERE label LIKE '%apple'

has 33 results.

[Artem Orobets]

Hi Justin,

Unfortunately FULLTEXT index does not allow to do that. To match whole label you can build NOTUNIQUE index.

Best regards,

Artem Orobets

Orient Technologies

the Company behind OrientDB

[Justin Harris]

Okay but if I also need FULLTEXT, then I could do:

SELECT FROM (SELECT expand(rid) FROM index:IX_English_label WHERE key = 'apple' ) WHERE label = 'apple'

or should I make another index that uses NOTUNIQUE?

Also, I'm still not sure why the index isn't being used automatically.

[Artem Orobets]

Yeah, the query your proposed should work but it is not quite efficient. If performance of the query is critical for you having of separate NOTUNIQUE index is more efficient.

As for optimization, I agree usage of FULLTEXT + filter is better then nothing. Could you create a ticket for that?

Best regards,

Artem Orobets

Orient Technologies

the Company behind OrientDB