Order By doesn't use indexes, slow performing when with a WHERE clause on just 150k rows

[Sky]

Hi

(using OrientDB Community 2.1.0)

I created a test data set of 150,000 films

I include cache counters, such as owns_count, loved_count, watched_count

I'm trying to write the most basic query

SELECT FROM film WHERE owns_count > 0 ORDER BY loved_count DESC

(user has applied a filter to show owned films , and ordered by most loved)

It's taking an unusable 2.5 secs

Removing the where clause or the order by allows the query to execute using the indexes (I have one for each counter, and also a composite for all three)

Can someone advise why this may be happening? I've tried lots of permutations, using LETs, subqueries, reference indexes direct etc but cannot get the WHERE and ORDER BY clauses working together well, performance always seems to take a hit as it avoids the index on the order.

Thanks

[Luca Garulli]

Hi Sky,

The SQL engine is not that smart. That's why we're completely rewriting it from scratch. In your case if a user want to see own films why are you querying the entire film class? I think it should be something like:

SELECT inV() FROM (

  SELECT outE('own_films') FROM #13:44

) ORDER BY loved_count DESC

IF:

- #13:44 is the current user 

- You have connected the films the user owns with "own_films" edge class

- You have the "love" counter in the edge as "loved_count"

Best Regards,

Luca Garulli

Founder & CEO

OrientDB

On 16 August 2015 at 17:22, Sky <s...@filmlovers.co.uk> wrote:

Hi

--

---
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 orient-databa...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Sky Viker-Rumsey]

Hi Luca

Because this isn't a view on just a users own library. Imagine they are browsing all films, and want to filter out / in the films of ones they have already actioned (and perhaps genres / years / actors etc but i think that will be ok as its edges)

You'll note my other post is about viewing a users own films and how best to do that.

Thanks

Sky 

Sent from myMail for iOS


Monday, 17 August 2015 19:18 +0100 from Luca Garulli <l.ga...@orientdb.com>:

You received this message because you are subscribed to a topic in the Google Groups "OrientDB" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/orient-database/r2_98lJLXDg/unsubscribe.
To unsubscribe from this group and all its topics, send an email to orient-databa...@googlegroups.com.