Hi All,
I have three different classes and CUI--->SENTENCE <--RELATION
CUI and SENTENCE are connected by hasCui edge.
SENTENCE and RELATION are connect by twrex edge.
I want to find vertices of class B 1) given 2 entries of class CUI 2) One entry from class CUI and one entry from Class RELATION.
I have indexes on ref_id of class A ref_id of class B and ref_id of class C. I also have indexes on hasCui edge.
Here are my queries:
orientdb {db=emerald}> explain select ref_id from (select expand(in('hasCui')) from CUI where ref_id='C0024530') where out('hasCui').ref_id contains 'C0013090'
rofiled command '{expandElapsed:1,limit:-1,evaluated:2709,current:#58:2852164,fetchingFromTargetElapsed:5124,documentReads:2709,user:#5:0,recordReads:2709,elapsed:5246.187,resultType:collection,resultSize:35}' in 5.247000 sec(s):
{"@type":"d","@version":0,"expandElapsed":1,"limit":-1,"evaluated":2709,"current":"#58:2852164","fetchingFromTargetElapsed":5124,"documentReads":2709,"user":"#5:0","recordReads":2709,"elapsed":5246.187,"resultType":"collection","resultSize":35,"@fieldTypes":"expandElapsed=l,evaluated=l,current=x,fetchingFromTargetElapsed=l,documentReads=l,user=x,recordReads=l,elapsed=f"}
orientdb {db=emerald}> explain select ref_id from (select expand(in('hasCui')) from CUI where ref_id='C0024530') where out('hasCui') contains 'C0013090' and out('twrex') contains 'treats'
Profiled command '{expandElapsed:0,limit:-1,evaluated:2709,current:#58:2852164,fetchingFromTargetElapsed:4880,documentReads:2709,user:#5:0,recordReads:2709,elapsed:4982.7866,resultType:collection,resultSize:0}' in 4.983000 sec(s):
{"@type":"d","@version":0,"expandElapsed":0,"limit":-1,"evaluated":2709,"current":"#58:2852164","fetchingFromTargetElapsed":4880,"documentReads":2709,"user":"#5:0","recordReads":2709,"elapsed":4982.7866,"resultType":"collection","resultSize":0,"@fieldTypes":"expandElapsed=l,evaluated=l,current=x,fetchingFromTargetElapsed=l,documentReads=l,user=x,recordReads=l,elapsed=f"}
Each query is taking approximately 5 seconds. It seems my queries are not using indexes. Also, I cannot afford to use lightweight edges as my edges has property. I have indexes on my edge hasCui_idx
Here are additional details of my database.
orientdb {db=emerald}> select count(*) from CUI
----+------+-------
# |@CLASS|count
----+------+-------
0 |null |2974236
----+------+-------
orientdb {db=emerald}> select count(*) from Sentence
----+------+-------
# |@CLASS|count
----+------+-------
0 |null |2853697
----+------+-------
orientdb {db=emerald}> select count(*) from RELATION
----+------+-----
# |@CLASS|count
----+------+-----
0 |null |12
----+------+-----
orientdb {db=emerald}> select count(*) from twrex
----+------+------
# |@CLASS|count
----+------+------
0 |null |466611
----+------+------
orientdb {db=emerald}> select count(*) from hasCui
----+------+--------
# |@CLASS|count
----+------+--------
0 |null |49970218
----+------+--------
orientdb {db=emerald}> list indexes
INDEXES
----------------------------------------------+------------+-----------------------+----------------+------------+
NAME | TYPE | CLASS | FIELDS | RECORDS |
----------------------------------------------+------------+-----------------------+----------------+------------+
AUI.ref_id | UNIQUE_... | AUI | ref_id | 8736353 |
AUI_0.ref_id | UNIQUE_... | AUI_0 | ref_id | 1747126 |
AUI_1.ref_id | UNIQUE_... | AUI_1 | ref_id | 1747277 |
AUI_2.ref_id | UNIQUE_... | AUI_2 | ref_id | 1747741 |
AUI_3.ref_id | UNIQUE_... | AUI_3 | ref_id | 1746487 |
AUI_4.ref_id | UNIQUE_... | AUI_4 | ref_id | 1747722 |
CUI.ref_id | UNIQUE_... | CUI | ref_id | 2974236 |
CUI_0.ref_id | UNIQUE_... | CUI_0 | ref_id | 594601 |
CUI_1.ref_id | UNIQUE_... | CUI_1 | ref_id | 594779 |
CUI_2.ref_id | UNIQUE_... | CUI_2 | ref_id | 594842 |
CUI_3.ref_id | UNIQUE_... | CUI_3 | ref_id | 594248 |
CUI_4.ref_id | UNIQUE_... | CUI_4 | ref_id | 594394 |
dictionary | DICTIONARY | | | 0 |
ENTITY.ref_id | UNIQUE_... | ENTITY | ref_id | 22 |
hasCui_IDX | NOTUNIQUE | hasCui | in | 44048388 |
| | | out | |
LUI.ref_id | UNIQUE_... | LUI | ref_id | 6117226 |
LUI_0.ref_id | UNIQUE_... | LUI_0 | ref_id | 1223326 |
LUI_1.ref_id | UNIQUE_... | LUI_1 | ref_id | 1223955 |
LUI_2.ref_id | UNIQUE_... | LUI_2 | ref_id | 1223304 |
LUI_3.ref_id | UNIQUE_... | LUI_3 | ref_id | 1223100 |
LUI_4.ref_id | UNIQUE_... | LUI_4 | ref_id | 1223541 |
ORole.name | UNIQUE | ORole | name | 3 |
OUser.name | UNIQUE | OUser | name | 3 |
RELATION.ref_id | UNIQUE_... | RELATION | ref_id | 12 |
SEMANTIC_TYPE.ref_id | UNIQUE_... | SEMANTIC_TYPE | ref_id | 138 |
SEMANTIC_TYPE.TUI | UNIQUE | SEMANTIC_TYPE | TUI | 133 |
SUI.ref_id | UNIQUE_... | SUI | ref_id | 7143364 |
SUI_0.ref_id | UNIQUE_... | SUI_0 | ref_id | 1429203 |
SUI_1.ref_id | UNIQUE_... | SUI_1 | ref_id | 1428665 |
SUI_2.ref_id | UNIQUE_... | SUI_2 | ref_id | 1428526 |
SUI_3.ref_id | UNIQUE_... | SUI_3 | ref_id | 1428373 |
SUI_4.ref_id | UNIQUE_... | SUI_4 | ref_id | 1428597 |
----------------------------------------------+------------+-----------------------+----------------+------------+
TOTAL = 32 93989685 |
-----------------------------------------------------------------------------------------------------------------+
Appreciate your help,
Thanks,
Regards,
Bharath