poor performance for order clause

[martingg88]

The following query take about 2 to 3 seconds from 80k records even though i already index out property. any idea?

SELECT in AS rid FROM list WHERE out = #16:5 ORDER BY (@Rid DESC) LIMIT 13

[wolf4ood]

can you post the explain of this query?

EXPLAIN SELECT in AS rid FROM list WHERE out = #16:5 ORDER BY (@rid DESC) LIMIT 13

[martingg88]

here is the explain:

{
    "result": [
        {
            "@type": "d",
            "@version": 0,
            "documentReads": 35205,
            "fullySortedByIndex": false,
            "documentAnalyzedCompatibleClass": 35205,
            "recordReads": 35205,
            "fetchingFromTargetElapsed": 440,
            "indexIsUsedInOrderBy": false,
            "compositeIndexUsed": 1,
            "current": "#68:35954",
            "involvedIndexes": [
                "list.out"
            ],
            "limit": 13,
            "orderByElapsed": 4,
            "evaluated": 35205,
            "elapsed": 450.49084,
            "resultType": "collection",
            "resultSize": 13,
            "@fieldTypes": "documentReads=l,documentAnalyzedCompatibleClass=l,recordReads=l,fetchingFromTargetElapsed=l,compositeIndexUsed=l,current=x,involvedIndexes=e,orderByElapsed=l,evaluated=l,elapsed=f"
        }
    ],
    "notification": "Query executed in 0.659 sec. Returned 1 record(s)"
}

[martingg88]

any idea to improve performance for order by alias property like following query?

SELECT in AS rid , in.ctime as ctime FROM list WHERE out = #16:5 ORDER BY (ctime DESC) LIMIT 13

[martingg88]

no update?

[wolf4ood]

how much is the execution time?

[martingg88]

more than 20 seconds for 30k records. feel very upset about this database already as we don't think it is capable for production.

[wolf4ood]

@martingg88

can you post the explain of this

SELECT in AS rid , in.ctime as ctime FROM list WHERE out = #16:5 ORDER BY (ctime DESC) LIMIT 13

and the result of this?

select count (*) from list

Thanks

[martingg88]

can you try it at your side with database has 30k records.

I'm sure you will have really suck performance for order by alias property.

[wolf4ood]

hi @martingg88

we could speed up things if you can send me a test db where i can try your queries and help you optimize it.

My email is e.risa@orientdb.com

[martingg88]

guys the issue is your side didn't optimize performance for query that have order clause with alias property. Please check with you team. I'm sorry that i can't send you the db as it is confidential data for my client.