Hi Hung,
Your title is misleading: "The query execution is unstable, sometimes fetch
more data". Why is fetching more data? Did you receive more than 100
records?
About the reason of the difference in timing, it looks like the record
#20:1 has much more Contacts, right? This query is slow because it uses the
index only for the order by, not for the lookup:
* "documentAnalyzedCompatibleClass": 110866,*
If you have 10M of contacts, it scans all the clusters to evaluate all the
following conditions:
* (OwnedBy.EntityInfo.State <> 200) AND (Account = #20:0) AND
(EntityInfo.State = 0)*
If you had a graph structure, you could start from the Account and traverse
the connected contacts. So the following query:
SELECT @rid, EmailAddress
FROM Contact
WHERE (OwnedBy.EntityInfo.State <> 200) AND (Account = #20:0) AND
(EntityInfo.State = 0)
ORDER BY EmailAddress ASC skip 100 limit 100
Would be something like:
SELECT @rid, EmailAddress FROM (
SELECT expand( out('HasContacts') ) FROM #20:0
)
WHERE OwnedBy.EntityInfo.State <> 200 AND EntityInfo.State = 0
ORDER BY EmailAddress ASC SKIP 100 LIMIT 100
Best Regards,
Luca Garulli
Founder & CEO
OrientDB <http://orientdb.com/>
On 11 May 2016 at 19:41, Hung Tran <[email protected]> wrote:
> Hi,
>
> I am using OrientDB 2.1.8 on Windows 2012 server platform.
>
> My SQL is
>
>
> SELECT @rid, EmailAddress FROM Contact WHERE (OwnedBy.EntityInfo.State <>
> 200) AND (Account = #20:0) AND (EntityInfo.State = 0) ORDER BY
> EmailAddress ASC skip 100 limit 100
>
>
> When I do a filter with Account = #20:0 which contains about 120000
> contacts, the EXPLAIN is
>
> {
> "result": [
> {
> "@type": "d",
> "@version": 0,
> "fullySortedByIndex": true,
> "documentReads": 200,
> "current": "#41:11015",
> "documentAnalyzedCompatibleClass": 200,
> "recordReads": 200,
> "involvedIndexes": [
> "IX_Contact_EmailAddress"
> ],
> "fetchingFromTargetElapsed": 250,
> "indexIsUsedInOrderBy": true,
> "evaluated": 200,
> "elapsed": 251.53171,
> "resultType": "collection",
> "resultSize": 100,
> "@fieldTypes":
> "documentReads=l,current=x,documentAnalyzedCompatibleClass=l,recordReads=l,involvedIndexes=e,fetchingFromTargetElapsed=l,evaluated=l,elapsed=f"
> }
> ],
> "notification": "Query executed in 0.575 sec. Returned 1 record(s)"
> }
>
> there are only 200 records evaluated and took 0.575 sec.
>
> When I change my filter to Account = #20:1, the EXPLAIN is
>
> {
> "result": [
> {
> "@type": "d",
> "@version": 0,
> "fullySortedByIndex": true,
> "documentReads": 110866,
> "current": "#41:111016",
> "documentAnalyzedCompatibleClass": 110866,
> "recordReads": 110866,
> "involvedIndexes": [
> "IX_Contact_EmailAddress"
> ],
> "fetchingFromTargetElapsed": 5517,
> "indexIsUsedInOrderBy": true,
> "evaluated": 110866,
> "elapsed": 5514.2456,
> "resultType": "collection",
> "resultSize": 100,
> "@fieldTypes":
> "documentReads=l,current=x,documentAnalyzedCompatibleClass=l,recordReads=l,involvedIndexes=e,fetchingFromTargetElapsed=l,evaluated=l,elapsed=f"
> }
> ],
> "notification": "Query executed in 5.835 sec. Returned 1 record(s)"
> }
>
> There are 110866 records evaluated and took 5.835 sec.
>
> I could not understand what caused a big different here, is it a bug? Any
> explanation 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.
