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 Technologiesthe Company behind OrientDB*
2014-04-23 22:01 GMT+03:00 Justin Harris <justindhar...@gmail.com>:
> 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.
>
>
> On Wednesday, April 23, 2014 1:25:13 AM UTC-4, Artem Orobets wrote:
>
>> 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 Technologiesthe Company behind OrientDB*
>>
>>
>> 2014-04-23 1:58 GMT+03:00 Justin Harris <justin...@gmail.com>:
>>
>> 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.
>>>
>>>
>>> On Tuesday, April 22, 2014 6:36:53 PM UTC-4, Artem Orobets wrote:
>>>
>>>> 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*
>>>>
>>>>
>>>> 2014-04-22 19:10 GMT+03:00 Justin Harris <justin...@gmail.com>:
>>>>
>>>>> 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
>>>>> fetchingFromTargetElapseddocumentReadsdocumentAnalyzedCompatibleClas
>>>>> s recordReads elapsed resultType resultSize 0 [1]
>>>>> #12:117790<http://localhost:2480/studio/index.html#/database/MKB/browse/edit/12:117790>23071177911177911177912307.6055collection2
>>>>> explain SELECT * FROM Food WHERE label = 'apple'
>>>>>
>>>>> @version involvedIndexes current
>>>>> fetchingFromTargetElapseddocumentReadsdocumentAnalyzedCompatibleClas
>>>>> s recordReads elapsed resultType resultSize 0 [1]
>>>>> #12:117790<http://localhost:2480/studio/index.html#/database/MKB/browse/edit/12:117790>22911177911177911177912292.1934collection2
>>>>> 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.
>>>>>
>>>>> --
>>>>>
>>>>> ---
>>>>> 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.
>>>>>
>>>>
>>>> --
>>>
>>> ---
>>> 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.
>>>
>>
>> --
>
> ---
> 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-database+unsubscr...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.
>
--
---
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-database+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
