My OrientDB 3.2 application can have millions of vertices and edges. The query below fetches the records from superclass V.
select FROM `V`
where class_name = ['Database', 'Table'] AND is_asset_class !='true'
order by name offset 0 limit 20
That is working fine and returns records within milliseconds.
select FROM `V`
where class_name = ['Database', 'Table'] AND is_asset_class !='true'
order by name offset 2000000 limit 20
When I use that offset the query takes 20-25 seconds.
offset 0: 0.127 sec
offset 20000: 0.4 sec
offset 200000: 2.56 sec
offset 2000000: 21.6 sec
I created a composite index on class_name and name. Order by on the name is a must for the result to be sorted alphabetically.
What should I change in the query to improve the performance?
This long wait happen because OrientDB need to trough and check all the records before being able to return the 20 that you need, probably the best way to keep the performance constant would be implement this use case in a different way if possible, which is:
select FROM `V`
where class_name = ['Database', 'Table'] AND is_asset_class !='true'
and name > :lastNameResultOfPreviousQuery
order by name limit 20
assuming that you have and index on the name property, this should be much faster.
Probably some optimization could be done in OrientDB itself to make this kind of use cases faster, probably is better to open an issue on the github repo to see if something can be done about it.
Bye