How to speed up a query with non-indexable field in WHERE

[Hung Tran]

Hi,

I have a schema like this

CREATE CLASS EntityInfo
  CREATE PROPERTY EntityInfo.State INTEGER

CREATE CLASS Contact
   CREATE PROPERTY Contact.EntityInfo EMBEDDED EntityInfo

I have tried to create an index like this, but it is failed to execute

CREATE INDEX Contact.EntityInfo_State ON Contact (EntityInfo.State) NOTUNIQUE_HASH_INDEX

and the error is 

com.orientechnologies.orient.core.index.OIndexException: Index with name : 'Contact.EntityInfo_State' cannot be created on class : 'Contact' because field: 'EntityInfo.State' is absent in class definition

because I could not create this kind of index, so all queries with State is very slow. For example,

Any idea will be very appreciated!

My Best,

Hung Tran

[user.w...@gmail.com]

Hi Hung,

you can't create an index in this way, if you want create it on EntityInfo.State property you can use this command:

CREATE INDEX EntityInfo.State ON EntityInfo (State) NOTUNIQUE_HASH_INDEX

Hope it helps.

Regards,

Michela

[Hung Tran]

Hi Michela,

I already have such index, but it does not help to speed up. That is really causing a major issue for my application on tables which have more few ten thousand of records.

In ORM world, a component like EntityInfo is really useful. In a relational database, I could create a mapped column like EntityInfo_State, so it could be still index-able, but it seems to be impossible with OrientDB for now.

Any idea will be really appreciated.

My Best,

Hung Tran

[alessand...@gmail.com]

Hi, 

you can try with 

select count(*) from (select expand(Entity) from Contact) where State=0

Let me know.

Regards,

Alessandro

[Hung Tran]

Hi Alessandro,

Just try it, 

From Studio, I get an error 

java.lang.OutOfMemoryError: GC overhead limit exceeded

From Server console, I see

[TIP] Query 'select expand(EntityInfo) from Contact' returned a result set with more than 10000 records. Check if you really need all these records, or reduce the resultset by using a LIMIT to improve both performance and used RAM [OEnterpriseProfiler]

And here is explain for "SELECT count(*) from Contact WHERE EntityInfo.State = 0", it seems that do a fully cluster scan

Is there any clue?

My Best,

Hung Tran

[alessand...@gmail.com]

Hi,

might help you this https://github.com/orientechnologies/orientdb/issues/3441

Regards,

Alessandro

[Hung Tran]

Hi Alessandro,

Just look at 3441, that won't help and still in Open. Furthermore, the case in 3441 could be speed up by creating an index on columns in ORDER BY. The situation here is very different, I could not create an index due to a restriction from Syntax.

My Best,

Hung Tran

[Luca Son]

Hi Hung Tran,

a possible approach would be to create an index SB -TREE EntityInfo.State NOTUNIQUE instead of a NOTUNIQUE_HASH_INDEX to avoid the sorting in the use of this index . This is because the indexes SB - TREE retain sorting , making it unnecessary sorting operations (like ORDER BY).

Hope it helps

[Hung Tran]

Hi Luca,

The system does not allow me to do that,

CREATE INDEX Contact.EntityInfo_State ON Contact (EntityInfo.State) NOTUNIQUE

will generate an error

com.orientechnologies.orient.core.index.OIndexException: Index with name : 'Contact.EntityInfo_State' cannot be created on class : 'Contact' because field: 'EntityInfo.State' is absent in class definition.

I could not create any kind of index on a component field aka a field of embedded class.

In ORM world, a component like EntityInfo is really useful. In a relational database, I could create a mapped column like EntityInfo_State, so it could be still index-able, but it seems to be impossible with OrientDB for now.

My Best,

Hung Tran

[Luca Son]

Hi Hung Tran,

I understand, unfortunately it is not currently possible to create indexes across multiple classes.

Could you try with this SB-TREE index ?

CREATE INDEX EntityInfo.State ON EntityInfo (State) NOTUNIQUE

instead of your HASH_INDEX




CREATE INDEX EntityInfo.State ON EntityInfo (State) NOTUNIQUE_HASH_INDEX


Luca

[Hung Tran]

Hi Luca,

I tried, the result is the same. I used HASH_INDEX because I don't need ORDER BY on that, so HASH_INDEX should give the best performance for WHERE.

My Best,

Hung Tran