Index optimisation

[Tobie Morgan Hitchcock]

Hi,

I've been working with OrientDB for a while, and have come across some potential index problems, which might be bugs, but I am not sure, so I thought I would check here first.

A detailed explanation of the index issues are on the following Gist:

https://gist.github.com/tobiemh/807e4604ab8f74ae8a6a

To summarise:

• Problem 1: The less optimum index from the base class is used instead of the more optimum index on the inherited class, making it inefficient with many records.
  
• Problem 2: The index used in the query is chosen based on the order in which it was created. In addition, it does not use multiple indexes to satisfy the query, making it inefficient with many records.
• Problem 3: Using a fulltext index at the same time as another index does not work. Therefore you must use subqueries to limit the result, which is very inefficient when there are many records.

Looking forward to hearing anyone's thoughts

Tobie

[Keith Freeman]

Good post, showing some important limitations of orientdb indexing.  Your "Problem 2" has been an especially important issue for us, I hope they can address it.

BTW, which version are you using?

[Tobie Morgan Hitchcock]

We are using OrientDB 2.0.2, what about you?

Are there any other issues you have found with indexing?

[Tobie Morgan Hitchcock]

I think there might be another issue with indexing...

Doing a COUNT in sql uses the index, but still reads every record, instead of just issuing the count on the index.

I think the issue has already been submitted here https://github.com/orientechnologies/orientdb/issues/3462

And i've added it to the gist to keep track of it...

https://gist.github.com/tobiemh/807e4604ab8f74ae8a6a