How to index a query properly

[Hung Tran]

Hi,

I am using OrientDB v.1.7.8, and a Task table with indices as following

and my query is 

"

     SELECT  FROM Task 

      WHERE (((DueTime <= '2015-12-28 09:33:02') AND (Status = 0)) OR ((ProcessId = 1) AND (Status = 10))) AND (EntityInfo.State = 0) 

ORDER BY Priority DESC, DueTime DESC, @rid ASC limit 200

"

(I am using named parameters)

when the Task table contains no row, the query is really fast (about 0.0002 second)

but when it contains around 2k rows, the query is slower (about 0.4 seconds)

and when it contains more than 200k rows, the query takes 11+ seconds with 0 rows in returning.

How should I index the table to get the best performance for the query above?

Any idea will be really appreciated!

My Best,

Hung Tran

[alessand...@gmail.com]

Hi,

did you put an index on the property State of EntityInfo ?

Best regards,

Alessandro

[Hung Tran]

Hi Alessandro,

I did not put index on State column, because they only have 4 different values, I think a normal comparison will be much faster than lookup index tree. Anyway, if there is no solution, I will do it.

My Best,

Hung Tran

[alessand...@gmail.com]

Hi,

have you tried to import your db into the version 2.1.8 ?

Alessandro