How to speed up Count query

[Hung Tran]

Hi,

My application is running on OrientDB v2.1.8, the Contact table consists of 1000002 rows.

I have tried many way to write a Count query but the performance is a pain at this point.

SELECT count(*) FROM Contact WHERE (Account = #20:1) AND (EntityInfo.State = 0)

=> Query executed in 25.481 sec. Returned 1 record(s) => 100000

SELECT count(*) FROM Contact WHERE (Account = #20:1)

=> Query executed in 24.245 sec. Returned 1 record(s) => 100000

SELECT count(@this[Account = #20:1]) FROM Contact

=> Query executed in 32.727 sec. Returned 1 record(s) => 100000

SELECT count(*) FROM Contact

=> Query executed in 0.279 sec. Returned 1 record(s) => 1000002

Here under is the Contact class and Index on this class

Any help will be very appreciated!

My Best,

Hung Tran

[alessand...@gmail.com]

Hi,

if you insert an index on the property Account ( NOTUNIQUE_HASH_INDEX) you should improve the performances.

Kind regards,

Alessandro

[Hung Tran]

Hi,

In my latest tests before migrating from 1.7.8 to version 2.1.8. With earlier version, an kind of index index is required on Links, but since 2.1+ it will be much slower if an index is created on a LINK property. I guess LINK will now use a kind of internal identifier indexing.

However, I have tried to add such index on Account property, it's really much faster. I will review again why I made a decision to remove index on Links.

Thank you very much!

My Best,

Hung Tran