Hi Garet, Your problem is absence of where clause, in such case if you would like to fetch 10 items ordered by updatedAt use following approach
select from indexvaluesdesc:Post.updatedAt limit 10 general rule is following. If you have where clause, then indexes are used to fetch data by where clause will be used in order by, otherwise you should use indexvalues: or indexvaluesdesc: More about this here https://github.com/orientechnologies/orientdb/issues/2539#issuecomment-49832077 and here https://github.com/orientechnologies/orientdb/wiki/SQL-Query#Target . Sorry for later answer, but hope it will solve all your problems. We use this approach because of limitations of current SQL engine. Also please do not use index names with dots it may lead to issues during parsing of query. So better create index as CREATE INDEX Post_updatedAt ON Post (updatedAt) NOTUNIQUE And then select from indexvaluesdesc:Post_updatedAt limit 10 In short future we are going to rewrite index engine and such kind of problems will gone. Looking forward for your feedback. On Mon, Jul 28, 2014 at 9:43 PM, Garrett Gottlieb < [email protected]> wrote: > Yes, here it is: > > orientdb {pumpup}> CREATE INDEX Post.updatedAt ON Post (updatedAt) > NOTUNIQUE > > > > > Creating index... > Created index successfully with 2719297 entries in 141.636002 sec(s). > > > > > Index created successfully > orientdb {pumpup}> explain select from Post order by updatedAt desc limit > 10 > > > Profiled command > '{fullySortedByIndex:true,documentReads:10,current:#22:2719287,documentAnalyzedCompatibleClass:10,recordReads:10,involvedIndexes:[1],fetchingFromTargetElapsed:17,indexIsUsedInOrderBy:true,evaluated:10,elapsed:33.603,resultType:collection,resultSize:10}' > in 0.101000 sec(s): > {"@type":"d","@version":0,"fullySortedByIndex":true,"documentReads":10, > "current":"#22:2719287","documentAnalyzedCompatibleClass":10,"recordReads" > :10,"involvedIndexes":["Post.updatedAt"],"fetchingFromTargetElapsed":17, > "indexIsUsedInOrderBy":true,"evaluated":10,"elapsed":33.603,"resultType": > "collection","resultSize":10,"@fieldTypes": > "documentReads=l,documentAnalyzedCompatibleClass=l,recordReads=l,involvedIndexes=e,fetchingFromTargetElapsed=l,evaluated=l,elapsed=f" > } > > > On Monday, July 28, 2014 5:08:40 AM UTC-4, Enrico Risa wrote: > >> Hi Garrett >> >> can you provide the explain of >> >> explain select from Post order by updatedAt desc limit 10 >> >> with index created on Post class? >> >> Thanks >> Enrico >> >> >> 2014-07-28 7:37 GMT+02:00 Garrett Gottlieb <[email protected]>: >> >> Looks like a potential bug with the abstract index. >> >> Building the index on Post reduced the query from 10 seconds to 0.017 >> seconds: (explain included) >> orientdb {pumpup}> CREATE INDEX Post.updatedAt ON Post (updatedAt) >> NOTUNIQUE >> >> >> >> >> Creating index... >> Created index successfully with 2719297 entries in 168.957993 sec(s). >> >> >> >> >> Index created successfully >> orientdb {pumpup}> select from Post order by updatedAt desc limit 10 >> >> >> ----+---------+------+-------+--------+---------+---------+- >> --------+---------+---------+---------+---------+---------+- >> --------+----------------- >> # |@RID |_allow|private|actionId|likeCount|objectId >> |isOptimiz|commentCo|hashtagsP|createdAt|updatedAt|ACL |user >> |dataCache >> ----+---------+------+-------+--------+---------+---------+- >> --------+---------+---------+---------+---------+---------+- >> --------+----------------- >> 0 |#22:27...|[1] |false |1 |0 |CaWJkr...|true |0 >> |true |2014-0...|2014-0...|{CMvKU...|{__typ...|{user={\"setti... >> 1 |#22:27...|[1] |false |1 |0 |LFAaO2...|true |0 >> |true |2014-0...|2014-0...|{*={re...|{__typ...|{user={\"setti... >> 2 |#22:27...|[1] |false |1 |0 |CuTTUz...|true |0 >> |true |2014-0...|2014-0...|{mcAlf...|{__typ...|{user={\"setti... >> 3 |#22:27...|[1] |false |1 |0 |fdy9X2...|true |0 >> |true |2014-0...|2014-0...|{*={re...|{__typ...|{user={\"setti... >> 4 |#22:27...|[1] |false |1 |3 |fSyNBu...|true |0 >> |true |2014-0...|2014-0...|{lM6Fp...|{__typ...|{user={\"setti... >> 5 |#22:27...|[1] |false |1 |0 |h8ogaR...|true |0 >> |true |2014-0...|2014-0...|{x3ikH...|{__typ...|{user={\"setti... >> 6 |#22:27...|[1] |false |1 |0 |ew2bqg...|true |0 >> |true |2014-0...|2014-0...|{YZHrd...|{__typ...|{user={\"setti... >> 7 |#22:27...|[1] |false |1 |0 |crlcnr...|true |0 >> |true |2014-0...|2014-0...|{HYVI5...|{__typ...|{user={\"setti... >> 8 |#22:27...|[1] |false |1 |0 |VYqt3L...|true |0 >> |true |2014-0...|2014-0...|{*={re...|{__typ...|{user={\"setti... >> 9 |#22:27...|[1] |false |1 |0 |9DzjOy...|true |0 >> |true |2014-0...|2014-0...|{Cmx5G...|{__typ...|{user={\"setti... >> ----+---------+------+-------+--------+---------+---------+- >> --------+---------+---------+---------+---------+---------+- >> --------+----------------- >> >> >> 10 item(s) found. Query executed in 0.017 sec(s). >> orientdb {pumpup}> drop index Post.updatedAt >> >> >> >> >> Removing index... >> Dropped index in 0.238000 sec(s). >> >> >> >> >> Index removed successfully >> orientdb {pumpup}> select from Post order by updatedAt desc limit 10 >> >> >> ----+---------+------+-------+--------+---------+---------+- >> --------+---------+---------+---------+---------+---------+- >> --------+----------------- >> # |@RID |_allow|private|actionId|likeCount|objectId >> |isOptimiz|commentCo|hashtagsP|createdAt|updatedAt|ACL |user >> |dataCache >> ----+---------+------+-------+--------+---------+---------+- >> --------+---------+---------+---------+---------+---------+- >> --------+----------------- >> 0 |#22:27...|[1] |false |1 |0 |CaWJkr...|true |0 >> |true |2014-0...|2014-0...|{CMvKU...|{__typ...|{user={\"setti... >> 1 |#22:27...|[1] |false |1 |0 |LFAaO2...|true |0 >> |true |2014-0...|2014-0...|{*={re...|{__typ...|{user={\"setti... >> 2 |#22:27...|[1] |false |1 |0 |CuTTUz...|true |0 >> |true |2014-0...|2014-0...|{mcAlf...|{__typ...|{user={\"setti... >> 3 |#22:27...|[1] |false |1 |0 |fdy9X2...|true |0 >> |true |2014-0...|2014-0...|{*={re...|{__typ...|{user={\"setti... >> 4 |#22:27...|[1] |false |1 |3 |fSyNBu...|true |0 >> |true |2014-0...|2014-0...|{lM6Fp...|{__typ...|{user={\"setti... >> 5 |#22:27...|[1] |false |1 |0 |h8ogaR...|true |0 >> |true |2014-0...|2014-0...|{x3ikH...|{__typ...|{user={\"setti... >> 6 |#22:27...|[1] |false |1 |0 |ew2bqg...|true |0 >> |true |2014-0...|2014-0...|{YZHrd...|{__typ...|{user={\"setti... >> 7 |#22:27...|[1] |false |1 |0 |crlcnr...|true |0 >> |true |2014-0...|2014-0...|{HYVI5...|{__typ...|{user={\"setti... >> 8 |#22:27...|[1] |false |1 |0 |VYqt3L...|true |0 >> |true |2014-0...|2014-0...|{*={re...|{__typ...|{user={\"setti... >> 9 |#22:27...|[1] |false |1 |0 |9DzjOy...|true |0 >> |true |2014-0...|2014-0...|{Cmx5G...|{__typ...|{user={\"setti... >> ----+---------+------+-------+--------+---------+---------+- >> --------+---------+---------+---------+---------+---------+- >> --------+----------------- >> >> >> 10 item(s) found. Query executed in 10.676 sec(s). >> orientdb {pumpup}> explain select from Post order by updatedAt desc >> limit 10 >> >> >> Profiled command '{fullySortedByIndex:true,documentReads:479025,current:# >> 22:2719287,documentAnalyzedCompatibleClass:10,recordReads:479025, >> involvedIndexes:[1],fetchingFromTargetElapsed:4550,indexIsUsedInOrderBy: >> true,evaluated:10,elapsed:4551.123,resultType:collection,resultSize:10}' >> in 4.558000 sec(s): >> {"@type":"d","@version":0,"fullySortedByIndex":true,"documentReads": >> 479025,"current":"#22:2719287","documentAnalyzedCompatibleClass":10, >> "recordReads":479025,"involvedIndexes":["PumpUp.updatedAt"]," >> fetchingFromTargetElapsed":4550,"indexIsUsedInOrderBy":true,"evaluated": >> 10,"elapsed":4551.123,"resultType":"collection","resultSize":10, >> "@fieldTypes":"documentReads=l,documentAnalyzedCompatibleClas >> s=l,recordReads=l,involvedIndexes=e,fetchingFromTargetElapsed=l, >> evaluated=l,elapsed=f"} >> >> >> Here is the info (for clarity): >> orientdb {pumpup}> info class Post >> >> >> Class................: Post >> Super class..........: V >> Default cluster......: post (id=22) >> Supported cluster ids: [22] >> Cluster selection....: round-robin >> >> >> PROPERTIES >> -------------------------------+-------------+-------------- >> -----------------+-----------+----------+----------+-------- >> ---+-----------+----------+ >> NAME | TYPE | LINKED TYPE/CLASS >> | MANDATORY | READONLY | NOT NULL | MIN | MAX | COLLATE >> | >> -------------------------------+-------------+-------------- >> -----------------+-----------+----------+----------+-------- >> ---+-----------+----------+ >> workoutLog_pointerProcessed | BOOLEAN | null >> | false | false | false | | | default >> | >> weightLog_pointerProcessed | BOOLEAN | null >> | false | false | false | | | default >> | >> workout | EMBEDDEDMAP | STRING >> | false | false | false | | | default >> | >> activityLog | EMBEDDEDMAP | STRING >> | false | false | false | | | default >> | >> workoutLog | EMBEDDEDMAP | STRING >> | false | false | false | | | default >> | >> activityLog_pointerProcessed | BOOLEAN | null >> | false | false | false | | | default >> | >> workout_pointerProcessed | BOOLEAN | null >> | false | false | false | | | default >> | >> weightLog | EMBEDDEDMAP | STRING >> | false | false | false | | | default >> | >> user_pointerProcessed | BOOLEAN | null >> | false | false | false | | | default >> | >> user | EMBEDDEDMAP | STRING >> | false | false | false | | | default >> | >> replacedACL | BOOLEAN | null >> | false | false | false | | | default >> | >> createdAt | DATETIME | null >> | false | false | false | | | default >> | >> ACL | EMBEDDEDMAP | EMBEDDEDMAP >> | false | false | false | | | default >> | >> objectId | STRING | null >> | false | false | false | | | default >> | >> updatedAt | DATETIME | null >> | false | false | false | | | default >> | >> _allowDelete | LINKSET | OIdentity >> | false | false | false | | | default >> | >> _allowRead | LINKSET | OIdentity >> | false | false | false | | | default >> | >> >> _allow | LINKSET | OIdentity >> | false | false | false |<span style="colo >> >> ... > > -- > > --- > 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. > -- Best regards, Andrey Lomakin. Orient Technologies the Company behind OrientDB -- --- 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.
