Hi Garrett, Try using the CREATE INDEX syntax as described on (it's the SQL-92 standard syntax):
https://github.com/orientechnologies/orientdb/wiki/Indexes#create-an-index So try to drop the index and recreate as: *DROP INDEX PumpUp.updatedAtCREATE INDEX PumpUp.updatedAt ON PumpUp (updatedAt) NOTUNIQUE* Then try executing from console: *orientdb {pumpup}> info class PumpUp* Lvc@ On 25 July 2014 04:39, Garrett Gottlieb <[email protected]> wrote: > I ran a simple select query, ordering by an indexed field, `updatedAt`: > > orientdb {pumpup}> select from Post order by updatedAt desc limit 10 > > > > ----+---------+------+-------+--------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+--------- > # |@RID |_allow|private|actionId|likeCount|objectId > |relatedId|isOptimiz|commentCo|hashtagsP|mentionsP|relatedId|createdAt|updatedAt|activityL|note > |ACL |user |workoutLo > > ----+---------+------+-------+--------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+--------- > 0 |#22:86...|[1] |false |1 |0 |CBxUhP...|null |true > |0 |true |true |null |2014-0...|2014-0...|true > |null |{hbtHL...|{__typ...|null > 1 |#22:86...|[1] |false |1 |0 |3C3wd5...|null |true > |0 |true |true |null |2014-0...|2014-0...|true > |null |{J3VM6...|{__typ...|null > 2 |#22:86...|[1] |false |1 |0 |XY2DNd...|null |true > |0 |true |true |null |2014-0...|2014-0...|true > |null |{jFe6N...|{__typ...|null > 3 |#22:86...|[1] |true |207 |0 |X6liN6...|VLDCLM...|true > |0 |true |true |true |2014-0...|2014-0...|true > |Starti...|{EjupY...|{__typ...|{__typ... > 4 |#22:86...|[1] |false |1 |0 |4oatWY...|null |true > |0 |true |true |null |2014-0...|2014-0...|true > |null |{Dv0qQ...|{__typ...|null > 5 |#22:86...|[1] |false |1 |0 |wqN7vb...|null |true > |0 |true |true |null |2014-0...|2014-0...|true > |null |{*={re...|{__typ...|null > 6 |#22:86...|[1] |false |1 |0 |k61i6N...|null |true > |0 |true |true |null |2014-0...|2014-0...|true > |null |{*={re...|{__typ...|null > 7 |#22:86...|[1] |false |1 |0 |vfr0nH...|null |true > |0 |true |true |null |2014-0...|2014-0...|true > |null |{*={re...|{__typ...|null > 8 |#22:86...|[1] |false |1 |0 |n3NngA...|null |true > |0 |true |true |null |2014-0...|2014-0...|true > |null |{cCLtc...|{__typ...|null > 9 |#22:86...|[1] |true |1 |0 |5fhvdB...|null |true > |0 |true |true |null |2014-0...|2014-0...|true > |null |{xTiuh...|{__typ...|null > > ----+---------+------+-------+--------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+--------- > > > 10 item(s) found. Query executed in 61.539 sec(s). > > > Here's the query explain, class info, and class count: > orientdb {pumpup}> explain select from Post order by updatedAt desc limit > 10 > > Profiled command > '{fullySortedByIndex:true,documentReads:3161990,current:#22:868738,documentAnalyzedCompatibleClass:10,recordReads:3161990,involvedIndexes:[1],fetchingFromTargetElapsed:47158,indexIsUsedInOrderBy:true,evaluated:10,elapsed:47160.176,resultType:collection,resultSize:10}' > in 47.160999 sec(s): > > {"@type":"d","@version":0,"fullySortedByIndex":true,"documentReads":3161990,"current":"#22:868738","documentAnalyzedCompatibleClass":10,"recordReads":3161990,"involvedIndexes":["PumpUp.updatedAt"],"fetchingFromTargetElapsed":47158,"indexIsUsedInOrderBy":true,"evaluated":10,"elapsed":47160.176,"resultType":"collection","resultSize":10,"@fieldTypes":"documentReads=l,documentAnalyzedCompatibleClass=l,recordReads=l,involvedIndexes=e,fetchingFromTargetElapsed=l,evaluated=l,elapsed=f"} > > > > > 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 | > createdAt | DATETIME | null > | false | false | false | | | default | > replacedACL | BOOLEAN | 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 | | | default | > _allowUpdate | LINKSET | OIdentity > | false | false | false | | | default | > > -------------------------------+-------------+-------------------------------+-----------+----------+----------+-----------+-----------+----------+ > > > INDEXES (10 altogether) > -------------------------------+----------------+ > NAME | PROPERTIES | > -------------------------------+----------------+ > Post_workoutLog_pointerProcessed| workoutLog (+) | > | workoutLog_pointerProcessed| > Post_user_pointerProcessed | user (+) | > | user_pointerProcessed| > Post.activityLog_pointerProcessed| activityLog_pointerProcessed| > Post_workout_pointerProcessed | workout (+) | > | workout_pointerProcessed| > Post.weightLog_pointerProcessed| weightLog_pointerProcessed| > Post_weightLog_pointerProcessed| weightLog (+) | > | weightLog_pointerProcessed| > Post.workout_pointerProcessed | workout_pointerProcessed| > Post.workoutLog_pointerProcessed| workoutLog_pointerProcessed| > Post_activityLog_pointerProcessed| activityLog (+)| > | activityLog_pointerProcessed| > Post.user_pointerProcessed | user_pointerProcessed| > -------------------------------+----------------+ > > > > > orientdb {pumpup}> select count(*) from post > > > ----+------+------ > # |@RID |count > ----+------+------ > 0 |#-1:-1|868748 > ----+------+------ > > > 1 item(s) found. Query executed in 0.002 sec(s). > > > Here's the relevant SQL to create this class: > -- abstract vertex class > CREATE CLASS PumpUp ABSTRACT > CREATE PROPERTY PumpUp.objectId STRING > CREATE INDEX PumpUp.objectId UNIQUE_HASH_INDEX > CREATE PROPERTY PumpUp.createdAt DATETIME > CREATE PROPERTY PumpUp.updatedAt DATETIME > CREATE INDEX PumpUp.updatedAt NOTUNIQUE > CREATE PROPERTY PumpUp.ACL EMBEDDEDMAP EMBEDDEDMAP > CREATE PROPERTY PumpUp.replacedACL BOOLEAN > CREATE INDEX PumpUp.replacedACL NOTUNIQUE_HASH_INDEX > > > -- record level security > ALTER CLASS PumpUp SUPERCLASS ORestricted > ALTER CLASS V SUPERCLASS PumpUp > > > CREATE CLASS Post EXTENDS V > > What I find odd is the `PumpUp.updatedAt` index isn't listed as an index > on Post, which is understandable since Post inherits it from the PumpUp > abstract class. > > Any idea why this is so slow? > > I'm running 1.7.7 > > Thanks :) > > -- > > --- > 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.
