How to speed up OrderBy with Where

73 views
Skip to first unread message

David López Pérez

unread,
May 11, 2016, 7:28:53 AM5/11/16
to orient-...@googlegroups.com
Hi,

I am using OrientDB 2.1.8 on Windows 2012 server platform. I have a table about 543.100 records, with indices as following.


Here are some queries and its timing captured from OrientDB Studio.

(this is the query I need to speed up)

SELECT FROM Contact WHERE (OwnedBy.EntityInfo.State <> 200) AND (Account = #20:2) AND (EntityInfo.State = 0) ORDER BY EmailAddress ASC skip 10 limit 10
=> it took 6.356 sec.

SELECT FROM Contact WHERE (Account #20:2) AND (EntityInfo.State = 0) ORDER BY EmailAddress ASC skip 10 limit 10
=> it took 4.781 sec.

SELECT FROM Contact WHERE (OwnedBy.EntityInfo.State <> 200) AND (EntityInfo.State = 0) ORDER BY EmailAddress ASC skip 10 limit 10
=> it took 0.236 sec.

SELECT FROM Contact WHERE (OwnedBy.EntityInfo.State <> 200) AND (Account #20:2) AND (EntityInfo.State = 0) skip 10 limit 10
=> it took 0.074 sec.

As you see the result, the WHERE on Account cause a poor performance. If I remove ORDER BY EmailAddress and do a EXPLAIN, index Contact.Account is used and query speeds up.
What do I need to do to speed up this query? Any help will be very appreciated.

My Best, 
David López

Ivan Mainetti

unread,
May 11, 2016, 8:24:19 AM5/11/16
to OrientDB
Hello,

could you post the result of the EXPLAIN of those queries?

Thanks.
Ivan



Il giorno mercoledì 11 maggio 2016 13:28:53 UTC+2, David López Pérez ha scritto:
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.

David López Pérez

unread,
May 11, 2016, 10:10:13 AM5/11/16
to OrientDB
Hello Ivan,

This query...
SELECT FROM Contact WHERE (OwnedBy.EntityInfo.State <> 200) AND (Account = #20:2) AND (EntityInfo.State = 0) ORDER BY EmailAddress ASC skip 10 limit 10
is explained as
{
   
"result": [
       
{
           
"@type": "d",
           
"@version": 0,
           
"documentReads": 100000,
           
"fullySortedByIndex": false,
           
"documentAnalyzedCompatibleClass": 100000,
           
"recordReads": 100000,
           
"fetchingFromTargetElapsed": 5674,
           
"indexIsUsedInOrderBy": false,
           
"compositeIndexUsed": 1,
           
"current": "#41:260870",
           
"involvedIndexes": [
               
"Contact.Account"
           
],
           
"limit": 10,
           
"orderByElapsed": 78,
           
"evaluated": 100000,
           
"user": "#5:0",
           
"elapsed": 5772.321,
           
"resultType": "collection",
           
"resultSize": 10,
           
"@fieldTypes": "documentReads=l,documentAnalyzedCompatibleClass=l,recordReads=l,fetchingFromTargetElapsed=l,compositeIndexUsed=l,current=x,involvedIndexes=e,orderByElapsed=l,evaluated=l,user=x,elapsed=f"
       
}
   
],
   
"notification": "Query executed in 5.8 sec. Returned 1 record(s)"
}


This query...
SELECT FROM Contact WHERE (Account = #20:2) AND (EntityInfo.State = 0) ORDER BY EmailAddress ASC skip 10 limit 10
is explained as


{
   
"result": [
       
{
           
"@type": "d",
           
"@version": 0,
           
"documentReads": 100000,
           
"fullySortedByIndex": false,
           
"documentAnalyzedCompatibleClass": 100000,
           
"recordReads": 100000,
           
"fetchingFromTargetElapsed": 4215,
           
"indexIsUsedInOrderBy": false,
           
"compositeIndexUsed": 1,
           
"current": "#41:260870",
           
"involvedIndexes": [
               
"Contact.Account"
           
],
           
"limit": 10,
           
"orderByElapsed": 78,
           
"evaluated": 100000,
           
"user": "#5:0",
           
"elapsed": 4317.759,
           
"resultType": "collection",
           
"resultSize": 10,
           
"@fieldTypes": "documentReads=l,documentAnalyzedCompatibleClass=l,recordReads=l,fetchingFromTargetElapsed=l,compositeIndexUsed=l,current=x,involvedIndexes=e,orderByElapsed=l,evaluated=l,user=x,elapsed=f"
       
}
   
],
   
"notification": "Query executed in 4.355 sec. Returned 1 record(s)"
}

This query...
SELECT FROM Contact WHERE (OwnedBy.EntityInfo.State <> 200) AND (EntityInfo.State = 0) ORDER BY EmailAddress ASC skip 10 limit 10
is explained as
{
   
"result": [
       
{
           
"@type": "d",
           
"@version": 0,
           
"fullySortedByIndex": true,
           
"documentReads": 20,
           
"current": "#41:80999",
           
"documentAnalyzedCompatibleClass": 20,
           
"recordReads": 20,
           
"involvedIndexes": [
               
"IX_Contact_EmailAddress"
           
],
           
"fetchingFromTargetElapsed": 344,
           
"indexIsUsedInOrderBy": true,
           
"evaluated": 20,
           
"user": "#5:0",
           
"elapsed": 349.00833,
           
"resultType": "collection",
           
"resultSize": 10,
           
"@fieldTypes": "documentReads=l,current=x,documentAnalyzedCompatibleClass=l,recordReads=l,involvedIndexes=e,fetchingFromTargetElapsed=l,evaluated=l,user=x,elapsed=f"
       
}
   
],
   
"notification": "Query executed in 0.365 sec. Returned 1 record(s)"
}

And this query...
SELECT FROM Contact WHERE (OwnedBy.EntityInfo.State <> 200) AND (Account = #20:2) AND (EntityInfo.State = 0) skip 10 limit 10
is explained as
{
   
"result": [
       
{
           
"@type": "d",
           
"@version": 0,
           
"documentReads": 20,
           
"fullySortedByIndex": false,
           
"documentAnalyzedCompatibleClass": 20,
           
"recordReads": 20,
           
"fetchingFromTargetElapsed": 0,
           
"indexIsUsedInOrderBy": false,
           
"compositeIndexUsed": 1,
           
"current": "#41:260923",
           
"involvedIndexes": [
               
"Contact.Account"
           
],
           
"limit": 10,
           
"evaluated": 20,
           
"user": "#5:0",
           
"elapsed": 26.85809,
           
"resultType": "collection",
           
"resultSize": 10,
           
"@fieldTypes": "documentReads=l,documentAnalyzedCompatibleClass=l,recordReads=l,fetchingFromTargetElapsed=l,compositeIndexUsed=l,current=x,involvedIndexes=e,evaluated=l,user=x,elapsed=f"
       
}
   
],
   
"notification": "Query executed in 0.043 sec. Returned 1 record(s)"
}


Thanks,
David
Reply all
Reply to author
Forward
0 new messages