How to index a query properly

45 views
Skip to first unread message

Hung Tran

unread,
Dec 28, 2015, 9:08:04 AM12/28/15
to OrientDB
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

unread,
Dec 28, 2015, 10:44:26 AM12/28/15
to OrientDB
Hi,
did you put an index on the property State of EntityInfo ?

Best regards,
Alessandro

Hung Tran

unread,
Dec 28, 2015, 11:54:22 PM12/28/15
to OrientDB
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

unread,
Dec 29, 2015, 5:38:43 AM12/29/15
to OrientDB
Hi,
have you tried to import your db into the version 2.1.8 ?

Alessandro
Reply all
Reply to author
Forward
0 new messages