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

87 views
Skip to first unread message

Hung Tran

unread,
Jan 20, 2016, 11:03:51 AM1/20/16
to OrientDB
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

unread,
Jan 20, 2016, 4:38:33 PM1/20/16
to OrientDB
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

unread,
Jan 20, 2016, 10:04:22 PM1/20/16
to OrientDB
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

unread,
Jan 21, 2016, 10:55:14 AM1/21/16
to OrientDB
Hi, 
you can try with 

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

Let me know.

Regards,
Alessandro

Hung Tran

unread,
Jan 21, 2016, 11:18:51 AM1/21/16
to orient-...@googlegroups.com
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

unread,
Jan 21, 2016, 11:33:07 AM1/21/16
to OrientDB
Hi,

Regards,
Alessandro

Hung Tran

unread,
Jan 21, 2016, 11:57:18 AM1/21/16
to OrientDB
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

unread,
Jan 22, 2016, 2:14:14 AM1/22/16
to OrientDB
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

unread,
Jan 22, 2016, 3:40:50 AM1/22/16
to orient-...@googlegroups.com
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

unread,
Jan 22, 2016, 4:30:16 AM1/22/16
to OrientDB
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

unread,
Jan 22, 2016, 4:50:36 AM1/22/16
to OrientDB
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
Reply all
Reply to author
Forward
0 new messages