Query by @rid and sorting on a different indexed field slow

[Herve Eichwald]

Hello guys,

I would like to speed up the following use case, not sure how to do it:

I have a Vertex of Users and a Vertex of Articles

Users visit articles.

I want to get the list of non yet visited articles for a given user.

So I do:

select @rid from indexvaluesdesc:Article.publishDate where @rid = (select out('Visit') from User where guid = 'f60c1880-4c45-5efc-88cb-a54ed9a030bb') limit 1

which is rather slow (500ms) ti give me #13:0

select out('Visit') from User where guid = 'f60c1880-4c45-5efc-88cb-a54ed9a030bb' gives me 15 @rid and is fast

Note that the article has been inserted in the cluster in the publishDate desc order, so #13:0 is the latest.

When I do:

select @rid from Article where @rid not in (select out('Visit') from User where guid = 'f60c1880-4c45-5efc-88cb-a54ed9a030bb') limit 1

it gives me the response #13:0 in 17ms

Yet I need the index as my articles won't always be inserted in this order and the cluster can grow pretty big, (right now around 500 000 records) and most articles won't have been visited for a user

Any idea how I could do that?

Thanks

[Herve Eichwald]

In fact the problem can be reproduced with a simpler case:

I have an article cluster with 400 000 records

select from Article where @rid not in [#13:250000] limit 1     takes 30 ms

while select from indexvaluesasc:Article.publishDate where @rid not in [#13:250000] limit 1   takes 400ms

Why?  It should just have to examine the first article and see that @rid are different, so why is it so slow?

Thanks

[Herve Eichwald]

Simpler question here:

https://groups.google.com/forum/#!topic/orient-database/LpG4kQSyqc8

Le vendredi 17 avril 2015 11:57:34 UTC-4, Herve Eichwald a écrit :

[nagaraja sosale ramaswamy]

i could be wrong, but i suspect it is because when you are querying the index, the where clause is not filtering on the indexed field publishDate itself instead on @rid which is not part of the index.

maybe you could try composite index on publishDate and @rid? 

[Herve Eichwald]

Hi Thanks for taking the time to answer.

Indeed I assume it cannot use the index to do the where close, yet it should be faster because it should only have to fetch one article on compare the @rid  (limit 1), the first article in the index has a different @rid

Is it possible to create an index on @rid even if it not a property but a metadata? so something like Article.@rid?

I think to use composite index, you need to query on both fields in order, so in this case i would have to query on publishDate and @rid, yet I only query on @rid and only use publishDate for the sorting.