How to speed up OrderBy with Where

[David López Pérez]

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]

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]

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