Hi, The problem here is that the result set can be sorted using the index when you define ORDER BY Priority DESC, but when you add other conditions like ORDER BY Priority DESC, @rid ASC then the index becomes useless and the result set has to be sorted in memory. For the second case (ORDER BY Priority DESC, DueTime DESC) you can define a composite index on Priority and DueTime, this should speed up the query a lot. When @rid is involved in general, indexes are not helpful, because the rid cannot be indexed in current release
Thanks Luigi 2016-04-25 7:14 GMT+02:00 Hung Tran <[email protected]>: > Hi, > > I am using OrientDB 2.1.8 on Windows 2012 server platform. I have a table > about 1.117.549 records, with indices as following. > > Here are some queries and its timing captured from OrientDB Studio. > > SELECT FROM Task WHERE (((DueTime <= '2016-04-25 04:19:58') AND (Status = > 0)) OR ((ProcessId = 1) AND (Status = 10))) AND (EntityInfo.State = 0) ORDER > BY Priority DESC, DueTime DESC, @rid ASC limit 1 > => it took 80.978 sec. > > SELECT FROM Task WHERE (((DueTime <= '2016-04-25 04:19:58') AND (Status = > 0)) OR ((ProcessId = 1) AND (Status = 10))) AND (EntityInfo.State = 0) > ORDER BY Priority DESC, DueTime DESC limit 1 > => it took 81.544 sec. > > SELECT FROM Task WHERE (((DueTime <= '2016-04-25 04:19:58') AND (Status = > 0)) OR ((ProcessId = 1) AND (Status = 10))) AND (EntityInfo.State = 0) > ORDER BY Priority DESC, @rid ASC limit 1 > => it took 75.335 sec. > > SELECT FROM Task WHERE (((DueTime <= '2016-04-25 04:19:58') AND (Status = > 0)) OR ((ProcessId = 1) AND (Status = 10))) AND (EntityInfo.State = 0) > ORDER BY Priority DESC limit 1 > => it took 0.266 sec. > > SELECT FROM Task WHERE (((DueTime <= '2016-04-25 04:19:58') AND (Status = > 0)) OR ((ProcessId = 1) AND (Status = 10))) AND (EntityInfo.State = 0) > limit 1 > => it took 0.275 sec. > > > As you see the result, the ORDER BY on DueTime or @rid cause a poor > performance. What do I need to do to speed them up now, any help will be > very appreciated! > > My Best, > Hung Tran > > -- > > --- > You received this message because you are subscribed to the Google Groups > "OrientDB" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > For more options, visit https://groups.google.com/d/optout. > -- --- You received this message because you are subscribed to the Google Groups "OrientDB" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
