CODEWITHSUNDEEP
orientdb
Anton
Anton

Reputation: 313

Is Null in OrientDB

In my database IM_0609 OrientDB version 2.0.8 is a class MARKS:

CALIBRATION_DATE:date.
DEVICE_MARK:string
DEVICE_NAME:string
END_MARK_NUM:decimal
MARK_NUM:decimal
PERIOD:decimal
SERIAL_NUM:string

In the class MARKS of 42898973 rows and I have created the index as follows: CREATE INDEX MARK_NUM_END_MARK_NUM on MARKS(MARK_NUM,END_MARK_NUM) NOTUNIQUE

I run the following query quickly:

select * from MARKS where (MARK_NUM =84278511 AND END_MARK_NUM
=84278511 AND END_MARK_NUM IS NOT NULL)

1 item(s) found. Query executed in 0.097 sec(s).

And this requires a request to create an index or update query:

select * from MARKS where MARK_NUM =84278511 AND END_MARK_NUM IS NULL

The server displays the following message:

2015-05-12 15:46:43:129 INFO {db=IM_0609} [TIP] Query 'select * from MARKS where MARK
_NUM =84278511 AND END_MARK_NUM IS NULL' fetched more than 50000 records: to speed up
the execution, create an index or change the query to use an existent index [OProfiler
]

Q: Why do so runs the second query?

Upvotes: 2

Views: 3856

Answers (3)

Lasitha Weerasinghe
Lasitha Weerasinghe

Reputation: 147

Orientdb does not keep index for null values if you don't specifically said to do that. To do that you have to set the metadata tag as follows:

CREATE INDEX addresses ON Employee (address) notunique METADATA {ignoreNullValues : false}

But your first query

select * from MARKS where (MARK_NUM =84278511 AND END_MARK_NUM =84278511 AND END_MARK_NUM IS NOT NULL)

says specific value for the END_MARK_NUM=84278511 and therefore it can use the index and limit the record reads < 50000

But your second query 

select * from MARKS where MARK_NUM =84278511 AND END_MARK_NUM IS NULL

does not says a specific value for END_MARK_NUM and therefore it can't use your index. Therefore the number of record reads increases and it gives the tip saying "try to reduce the number of record reads that orientdb has to perform"

Edit: There seems to be a bug in the ignoreNullValues functionality. (https://github.com/orientechnologies/orientdb/issues/4508)

Upvotes: 1

Anton
Anton

Reputation: 313

This query runs instantly:

select * from index:MARKS.MARK_NUM_END_MARK_NUM where key=[84278511,NULL]

Q:But how do I get the fields of my class MARKS?

Upvotes: 0

Ziink
Ziink

Reputation: 193

Seems like you simply have too many records matching the query. Also, did you mean to have 'IS NOT NULL' in the first query but 'IS NULL' in the second?

Try with a limit

select * from MARKS where MARK_NUM =84278511 AND END_MARK_NUM IS NULL LIMIT 10

Upvotes: 1

Related Questions