Search code examples
orientdb

OrientDB query to receive last vertex before a given date

Let's say I have the following list of vertices (connected by edges) in the orient database:

 [t=1] --> [t=2] --> [t=3] --> [t=4] --> [t=5] --> [t=6] --> [t=7]

Each vertex has a timestamp t. I now want to receive the last vertex before a given date. Example: give me the last vertex before t=5, which is t=4.

Currently I'am using the following query to do this:

 SELECT FROM ANYVERTEX WHERE t < 5 ORDER BY t DESC LIMIT 1

This is working fine when having up to let's say 1000 elements but the performance of that query drops with the number of elements inserted in the list. I already tried using an index, which improved the overall performance, but the problem, that the performance drops with the amount of elements still persists.

Solution

  • When building queries, always try to use the information you have about the relationship in your query to improve performance. In this case you don't need the sort (which is an expensive operation) because you know that the vertex you need has an incoming edge to the vertex, you can simply use that information in your query.

    For example, let's say I have the following setup:

    CREATE CLASS T EXTENDS V
CREATE VERTEX T SET t = 1
CREATE VERTEX T SET t = 2
CREATE VERTEX T SET t = 3
CREATE VERTEX T SET t = 4
CREATE VERTEX T SET t = 5

CREATE CLASS link EXTENDS E
CREATE EDGE link FROM (SELECT * FROM T WHERE t = 1) TO (SELECT * FROM T WHERE t = 2)
CREATE EDGE link FROM (SELECT * FROM T WHERE t = 2) TO (SELECT * FROM T WHERE t = 3)
CREATE EDGE link FROM (SELECT * FROM T WHERE t = 3) TO (SELECT * FROM T WHERE t = 4)
CREATE EDGE link FROM (SELECT * FROM T WHERE t = 4) TO (SELECT * FROM T WHERE t = 5)

    Then I can select the vertex before any T as such:

    SELECT expand(in('link')) FROM T WHERE t = 2

    This query does the following:

    1. Select the vertex from T where t=2
    2. From that vertex, traverse the incoming edge(s) of type link
    3. expand() the vertex from which that edge comes from to get all of its information

    The result is exactly what you want:

    OrientDB example

    This should give better performance (especially if you add an index on the attribute t of the vertices) because you are using all the information you know in advance about the relationship = the node you need has an edge to the node you select.

    Hope that helps you out.