Hi Luca,
I've updated the index:
CREATE INDEX PumpUp.updatedAt ON PumpUp (updatedAt) NOTUNIQUE
orientdb {pumpup}> info class PumpUp
Class................: PumpUp
Super class..........: ORestricted
Default cluster......: null (id=-1)
Supported cluster ids: [-1]
Cluster selection....: round-robinBase classes.........: E, V
PROPERTIES
-------------------------------+-------------+-------------------------------+-----------+----------+----------+-----------+-----------+----------+
NAME | TYPE | LINKED TYPE/CLASS
| MANDATORY | READONLY | NOT NULL | MIN | MAX | COLLATE |
-------------------------------+-------------+-------------------------------+-----------+----------+----------+-----------+-----------+----------+
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 | | | default |
_allowUpdate | LINKSET | OIdentity
| false | false | false | | | default |
-------------------------------+-------------+-------------------------------+-----------+----------+----------+-----------+-----------+----------+
INDEXES (3 altogether)
-------------------------------+----------------+
NAME | PROPERTIES |
-------------------------------+----------------+
PumpUp.replacedACL | replacedACL |
PumpUp.objectId | objectId |
PumpUp.updatedAt | updatedAt |
-------------------------------+----------------+
But am still getting a very slow query:
orientdb {pumpup}> select count(*) from post; select from post order by
updatedAt desc limit 10
----+------+------
# |@RID |count
----+------+------
0 |#-1:-1|562449
----+------+------
1 item(s) found. Query executed in 0.005 sec(s).
----+---------+------+-------+--------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------
# |@RID |_allow|private|actionId|likeCount|relatedId|objectId
|isOptimiz|commentCo|note
|hashtagsP|relatedId|showInPop|mentionsP|activityL|createdAt|updatedAt|ACL
|user |workoutLo|activityL|details
----+---------+------+-------+--------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------
0 |#22:56...|[1] |false |1300 |0 |wCzAgA...|TcbNo7...|true
|0 |null |true |true |false |null |true
|2014-0...|2014-0...|{NKXBg...|{__typ...|null |{__typ...|null
1 |#22:56...|[1] |false |1300 |0 |qtD8fi...|DzDs8s...|true
|0 |Just d...|true |true |false |null |true
|2014-0...|2014-0...|{M5uAi...|{__typ...|null |{__typ...|null
2 |#22:56...|[1] |null |207 |0 |4xZ5m8...|XSPIL4...|true
|0 |null |true |true |null |null |null
|2013-0...|2014-0...|{*={re...|{__typ...|{__typ...|null |{"calo...
3 |#22:56...|[1] |null |207 |0 |SzYHuf...|rDI2gj...|true
|0 |null |true |true |null |null |null
|2013-0...|2014-0...|{*={re...|{__typ...|{__typ...|null |{"calo...
4 |#22:56...|[1] |null |207 |0 |fxDE6N...|Tq422o...|true
|0 |Good |true |true |null |null |null
|2013-0...|2014-0...|{*={re...|{__typ...|{__typ...|null |{"calo...
5 |#22:56...|[1] |null |207 |0 |bF0os7...|couPB8...|true
|0 |null |true |true |null |null |null
|2013-0...|2014-0...|{*={re...|{__typ...|{__typ...|null |{"calo...
6 |#22:56...|[1] |null |207 |0 |CAVFoT...|MVJRkX...|true
|0 |null |true |true |null |null |null
|2013-0...|2014-0...|{*={re...|{__typ...|{__typ...|null |{"calo...
7 |#22:56...|[1] |null |207 |0 |kJ1RTD...|sDrLhX...|true
|0 |null |true |true |null |null |null
|2013-0...|2014-0...|{d4CpX...|{__typ...|{__typ...|null |{"calo...
8 |#22:56...|[1] |false |1 |0 |null |Qqj1ru...|true
|0 |null |true |null |null |true |null
|2014-0...|2014-0...|{ldsbq...|{__typ...|null |null |null
9 |#22:56...|[1] |false |1 |0 |null |zVSfuW...|true
|0 |null |true |null |null |true |null
|2014-0...|2014-0...|{*={re...|{__typ...|null |null |null
----+---------+------+-------+--------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------
10 item(s) found. Query executed in 78.106 sec(s).
On Friday, July 25, 2014 8:03:15 AM UTC-4, Lvc@ wrote:
>
> 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]
> <javascript:>> 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 su
>
> ...
--
---
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.
