orientdb sql between not working as expected

Question

I have a orientdb database with a document style storage. I have a class MyDocument with about 300.000 records, stored in a round robin fashion (no custom clustering strategy).

I have a numeric field that tells me the storage day in yyyyMMdd fashion, that gets indexed not uniquely with an SBtree.

Until I do queries such as:

select from MyDocument where publishDay=20151101

it works nicely: I get about 100 records per day.

if I use a range the pain starts:

 select from MyDocument where publishDay between 20151101 and 20151102

If I do this query from the console it returns 0 records.

If I do this from the webapp it says

"fetched more than 50000 records: to speed up the execution, create an index or change the query to use an existent index"

but I already have an index, so it seems I cannot do more that queries on specific dates. also a trick such as:

select * from inputdocument where publishday = 20150102 or publishday = 20150101

BTW I use release 2.0.15

Am I doing something wrong? Have I reached the limit of orientdb with this kind of usage?

LucaS LucaS · Accepted Answer · 2015-11-20T02:58:02

I tried to reproduce your DB in OrientDB 2.0.15 with this small osql script:

create class MyDocument extends V;

create property MyDocument.publishDay integer;

create vertex MyDocument set publishDay = 20151101;
create vertex MyDocument set publishDay = 20151101;
create vertex MyDocument set publishDay = 20151101;
create vertex MyDocument set publishDay = 20151101;
create vertex MyDocument set publishDay = 20151101;
create vertex MyDocument set publishDay = 20151101;
create vertex MyDocument set publishDay = 20151102;
create vertex MyDocument set publishDay = 20151102;
create vertex MyDocument set publishDay = 20151102;
create vertex MyDocument set publishDay = 20151102;
create vertex MyDocument set publishDay = 20151102;
create vertex MyDocument set publishDay = 20151102;
create vertex MyDocument set publishDay = 20151103;
create vertex MyDocument set publishDay = 20151103;
create vertex MyDocument set publishDay = 20151103;
create vertex MyDocument set publishDay = 20151103;
create vertex MyDocument set publishDay = 20151103;
create vertex MyDocument set publishDay = 20151103;
create vertex MyDocument set publishDay = 20151104;
create vertex MyDocument set publishDay = 20151104;
create vertex MyDocument set publishDay = 20151104;
create vertex MyDocument set publishDay = 20151104;
create vertex MyDocument set publishDay = 20151104;
create vertex MyDocument set publishDay = 20151104;
create vertex MyDocument set publishDay = 20151105;
create vertex MyDocument set publishDay = 20151105;
create vertex MyDocument set publishDay = 20151105;
create vertex MyDocument set publishDay = 20151105;
create vertex MyDocument set publishDay = 20151105;
create vertex MyDocument set publishDay = 20151105;
create vertex MyDocument set publishDay = 20151106;
create vertex MyDocument set publishDay = 20151106;
create vertex MyDocument set publishDay = 20151106;
create vertex MyDocument set publishDay = 20151106;
create vertex MyDocument set publishDay = 20151106;
create vertex MyDocument set publishDay = 20151106;

create index MyDocument.publishDay NOTUNIQUE;

I declared the property "publishDay" as integer and created a NOTUNIQUE index on "publishDay".

and your query works

also searching by index

Are there any differences from your schema ?

orientdb sql between not working as expected

2 Answers