slow select from composite index

[Alexander Sahler]

Hello everyone.

I'm doing a proof of concept with orientdb (1.7.9). The use case (condensed) is to have a store from which I'm reserving an item for a certain sales transaction. Therefore I insert the items into an orient db. The following properties are given:

create class Item
create property Item.productid integer
create property Item.state string
create property Item.validFrom datetime
create property Item.validThru datetime

So, items are valid from a certain validFrom date and expire on validThru. State is initially 'n' like in 'n'ew.

Now, to find (and modify) a certain matching item of a given product, the query is:

update Item set state='r' where productid = 123 and state = 'n' and validFrom < sysdate() and validThru > sysdate() limit 1

To gain fast access i created an index:

create index Item.fastaccess on Item (productid, state, validFrom, validThru) notunique

While this works fine when items are in date range (item found) it does not when no item is matching because no item is 'valid'. In this case it takes 7 seconds to find out that no valid item exists.

For me it seems that index is not used for this query:

"result": [
        {
            "0": null,
            "@type": "d",
            "@version": 0,
            "limit": 1,
            "compositeIndexUsed": 1,
            "involvedIndexes": [
                1
            ],
            "fullySortedByIndex": false,
            "indexIsUsedInOrderBy": false,
            "current": "#11:3",
            "evaluated": 100000,
            "fetchingFromTargetElapsed": 7047,
            "documentReads": 100000,
            "documentAnalyzedCompatibleClass": 100000,
            "recordReads": 100000,
            "elapsed": 7048.1084,
            "resultType": "number",
            "@fieldTypes": "compositeIndexUsed=l,involvedIndexes=e,evaluated=l,fetchingFromTargetElapsed=l,documentReads=l,documentAnalyzedCompatibleClass=l,recordReads=l,elapsed=f"
        }
    ],
    "notification": "Query executed in 7.069 sec. Returned 1 record(s)"
}

What am I doing wrong?

Best regards, 
Alexander

[Alexander Sahler]

This issue seems to have something to do with date object type. When I change the property to type 'Long' and store the getTime() value in the property, the index works as desired and is fast also when no item matches the selection criteria.

So seems to be some issue with indexing Date objects!

Alexander

Am Freitag, 14. November 2014 14:13:02 UTC+1 schrieb Alexander

[Andrey Lomakin]

HI Alexander,

Could you create issue for this case ?

--

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

--

Best regards,
Andrey Lomakin.

[Alexander Sahler]

Done: https://github.com/orientechnologies/orientdb/issues/3129

--

---
You received this message because you are subscribed to a topic in the Google Groups "OrientDB" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/orient-database/lp9jotadpKM/unsubscribe.
To unsubscribe from this group and all its topics, send an email to orient-databa...@googlegroups.com.