orientdb count slow

[dewj]

orientdb {db=test}> select count(*) from v where index > 9

----+------
|count
----+------
0 |399990
----+------

1 item(s) found. Query executed in 19.8 sec(s).
orientdb {db=test}> select count(*) from v where index between 2 and 344444

----+------
|count
----+------
0 |344443
----+------

1 item(s) found. Query executed in 14.263 sec(s).

it take much time to count by where conditions.

1、#1537
2、https://groups.google.com/forum/#!topic/orient-database/2KJuo6DShxI

but noting to me,what can i do?

[lvca]

Like with RDBMS, create an index on "index" field:

create property v.index integer
create index v.index on V (index) UNIQUE

Change UNIQUE to NOTUNIQUE in case you can have duplicates on "index" field.

[dewj]

@lvca
orientdb {db=test}> create property v.index integer

Property created successfully with id=1

orientdb {db=test}> create index v.index on V (index) UNIQUE

Creating index...
Created index successfully with 400000 entries in 23.650000 sec(s).

Index created successfully

orientdb {db=test}> select count(*) from v where index between 9 and 329873

----+------
|count
----+------
0 |329865
----+------

1 item(s) found. Query executed in 7.94 sec(s).

it still 7.94 sec(s),while oracle take 0.078 sec(s).
i download orientdb-community-2.0-M2,how can reduce to 1 second?

[lvca]

To be sure it's using the index, can you execute:

explain select count(*) from v where index between 9 and 329873

[dewj]

orientdb {db=test}> explain select count(*) from v where index between 9 and 329873

Profiled command '{limit:-1,compositeIndexUsed:1,fullySortedByIndex:false,evalua
ted:329865,documentAnalyzedCompatibleClass:329865,projectionElapsed:47,involvedI
ndexes:[1],groupByElapsed:0,indexIsUsedInOrderBy:false,current:#13:329063,fetchi
ngFromTargetElapsed:10431,documentReads:329865,recordReads:329865,elapsed:10599.
837,resultType:collection,resultSize:1}' in 10.649000 sec(s):
{"@type":"d","@Version":0,"limit":-1,"compositeIndexUsed":1,"fullySortedByIndex"
:false,"evaluated":329865,"documentAnalyzedCompatibleClass":329865,"projectionEl
apsed":47,"involvedIndexes":["v.index"],"groupByElapsed":0,"indexIsUsedInOrderBy
":false,"current":"#13:329063","fetchingFromTargetElapsed":10431,"documentReads"
:329865,"recordReads":329865,"elapsed":10599.837,"resultType":"collection","resu
ltSize":1,"@fieldTypes":"compositeIndexUsed=l,evaluated=l,documentAnalyzedCompat
ibleClass=l,projectionElapsed=l,involvedIndexes=e,groupByElapsed=l,fetchingFromT
argetElapsed=l,documentReads=l,recordReads=l,elapsed=f"}
orientdb {db=test}>

[lvca]

Does this changes anything?

explain select count(*) from v where index >= 9 and index <= 329873

[dewj]

orientdb {db=test}> select count(*) from v where index >= 9 and index <= 329873

----+------
|count
----+------
0 |329865
----+------

1 item(s) found. Query executed in 7.673 sec(s).
orientdb {db=test}> explain select count(*) from v where index >= 9 and index <=
329873

Profiled command '{limit:-1,compositeIndexUsed:1,fullySortedByIndex:false,evalua
ted:329865,rangeQueryConvertedInBetween:1,documentAnalyzedCompatibleClass:329865
,projectionElapsed:32,involvedIndexes:[1],indexIsUsedInOrderBy:false,groupByElap
sed:0,current:#13:329063,fetchingFromTargetElapsed:7706,documentReads:329865,rec
ordReads:329865,elapsed:7721.0684,resultType:collection,resultSize:1}' in 7.7380
00 sec(s):
{"@type":"d","@Version":0,"limit":-1,"compositeIndexUsed":1,"fullySortedByIndex"
:false,"evaluated":329865,"rangeQueryConvertedInBetween":1,"documentAnalyzedComp
atibleClass":329865,"projectionElapsed":32,"involvedIndexes":["v.index"],"indexI
sUsedInOrderBy":false,"groupByElapsed":0,"current":"#13:329063","fetchingFromTar
getElapsed":7706,"documentReads":329865,"recordReads":329865,"elapsed":7721.0684
,"resultType":"collection","resultSize":1,"@fieldTypes":"compositeIndexUsed=l,ev
aluated=l,documentAnalyzedCompatibleClass=l,projectionElapsed=l,involvedIndexes=
e,groupByElapsed=l,fetchingFromTargetElapsed=l,documentReads=l,recordReads=l,ela
psed=f"}

[lvca]

Ok, seems the SQL engine doesn't use the index properly. @luigidellaquila is it possible something is changed in the optimizator?

[tobiemh]

+1

Even though the index appears to be used, the query still reads through all of the records, making any count(*) on large data sets slow and pretty much unusable.

OrientDB version: 2.0.2

[ajob61]

Any Progress on this? We are experiencing the same behaviour of indexes being used but the query still reading through the entire record data set which becomes particulary unusable on large data sets as mentioned by @tobiemh. We greatly appreciate a fix or a suggested work around.

[seeden]

+1 for fix of this

[dmikov]

Happy new year. It's has been a more then a year. Milestones keep changing. The performance of indexes are crucial to database usage. Do you have a real ETA on this. Our company needs to make decision about back end and while some driver issues are ok to wait on index problems are not ok. Please help with info.

[gerdhub]

+1 for fix of this, this is also related to #5945.

[andreipop-reea]

+1

[StarpTech]

+1

[TheAdamGalloway]

+1, this is mission critical for a lot of use cases.

[seeden]

run

[StarpTech]

@luigidellaquila any progress?

[gerdhub]

+1

[bbourgois]

+1