OrientDB supports 4 kinds of indexes:
| Index type | Durable | Transactional | Range queries | Best features | Description |
|---|---|---|---|---|---|
| SB-Tree | YES | YES | YES | Good mix of all | the default one is durable and transactional |
| Hash | YES | YES | no | Super fast lookup, very light on disk | Works like a HashMap so it's faster on punctual lookup (select from xxx where salary = 1000) and consumes less resources, but you cannot use it for range queries (select from xxx where salary between 1000 and 2000) |
| Lucene | YES | YES | YES | Good on full-text and spatial indexes | Lucene indexes can be used only for full-text and spatial |
Indexes can be handled like classes (or tables for RDBMS users) using the SQL language and prefixing with "index:" the index name. The index is like a class (or table) with 2 properties:
Indexes can be updated:
The index type cannot be changed once created. The supported index types are the following:
Lucene engine:
Any 3rd party index plugged
Every single database has a default manual index of type "DICTIONARY" called dictionary with Strings as keys. This is very useful to:
Creates a new index. To create an automatic index bound to a schema property use section "ON" of create index command or use as name the <class.property> notation. But assure to have created the schema for it before the index. See the example below.
Syntax:
CREATE INDEX <name> [ON <class-name> (prop-names)] <type> [<key-type>]
[METADATA {<metadata>}]
Where:
<class>.<property> to create an automatic index bound to a schema property. In this case class is the class of the schema and property, is the property created into the class. Notice that in another case index name can't contain '.' symbolExamples of custom index:
CREATE INDEX mostRecentRecords unique date
Examples of automatic index bound to the property "id" of class "User":
CREATE PROPERTY User.id BINARY
CREATE INDEX User.id UNIQUE
Another index for "id" property of class "User":
CREATE INDEX indexForId ON User (id) unique
Examples of index for "thumbs" property of class "Movie".
CREATE INDEX thumbsAuthor ON Movie (thumbs) unique
CREATE INDEX thumbsAuthor ON Movie (thumbs by key) unique
CREATE INDEX thumbsValue ON Movie (thumbs by value) unique
Example of composite index
CREATE PROPERTY Book.author STRING
CREATE PROPERTY Book.title STRING
CREATE PROPERTY Book.publicationYears EMBEDDEDLIST INTEGER
CREATE INDEX books ON Book (author, title, publicationYears) unique
For more information look at Create index command.
Drop an index. Linked records will be not removed. Syntax:
DROP INDEX <name>
Where:
For more information look at Drop index command.
Returns all the records with the requested key.
select from index:<index-name> where key = <key>
Example:
select from index:dictionary where key = 'Luke'
To set a case-insensitive match in index, set the COLLATE attribute of indexed properties to "ci" (stands for Case Insensitive). Example:
create index OUser.name on OUser (name collate ci) UNIQUE
Inserts a new entry in the index with key and rid.
insert into index:<index-name> (key,rid) values (<key>,<rid>)
Example:
insert into index:dictionary (key,rid) values ('Luke',#10:4)
Retrieves the key ranges between min and max.
select from index:<index-name> where key between <min> and <max>
Example:
select from index:coordinates where key between 10.3 and 10.7
Deletes all the entries with the requested key.
delete from index:<index-name> where key = <key>
Example:
delete from index:addressbook where key = 'Luke'
Deletes an entry by passing key and rid. Returns true if removed, otherwise false if the entry wasn't found.
delete from index:<index-name> where key = <key> and rid = <rid>
Example:
delete from index:dictionary where key = 'Luke' and rid = #10:4
Removes all the entries with the rid passed.
delete from index:<index-name> where rid = <rid>
Example:
delete from index:dictionary where rid = #10:4
Returns the number of entries on that index.
select count(*) as size from index:<index-name>
Example:
select count(*) as size from index:dictionary
Retrieves all the keys of the index.
select key from index:<index-name>
Example:
select key from index:dictionary
Retrieves all the entries of the index as pairs key and rid.
select key, value from index:<index-name>
Example:
select key, value from index:dictionary
Removes all the entries. The index will be empty after this call. This removes all the entries of an index.
delete from index:<index-name>
Example:
delete from index:dictionary
Indexes by default ignore null values. For such reason queries against NULL value that use indexes return no entries.
If you want to index also null values set { ignoreNullValues : false } as metadata. Example:
CREATE INDEX addresses ON Employee (address) notunique
METADATA {ignoreNullValues : false}
You can do the same operations with composite indexes.
A composite key is a collection of values by its nature, so syntactically it is defined as a collection. For example, if we have class book, indexed by its three fields:
We can use following query to look up a book:
select from index:books where key = ["Donald Knuth", "The Art of Computer Programming", 1968]
Or to look up a book within a publication year range:
select from index:books where key between ["Donald Knuth", "The Art of Computer Programming", 1960] and ["Donald Knuth", "The Art of Computer Programming", 2000]
This is a mechanism that allows searching index record by several first fields of its composite key. In this case the remaining fields with undefined value can match any value in result.
Composite indexes are used for partial match search only when the declared fields in composite index are used from left to right. Using the example above, if you search only for title, the composite index cannot be used, but it will be used if you search for author + title.
For example, if we don't care when books have been published, we can throw away the publication year field from the query. So, the result of the following query will be all books with this author and title and any publication year
select from index:books where key = ["Author", "The Art of Computer Programming"]
If we also don't know title, we can keep only author field in query. Result of following query will be all books of this author.
select from index:books where key = ["Donald Knuth"]
Or equivalent
select from index:books where key = "Donald Knuth"
In case of range queries, the field subject to the range must be the last one. Example:
select from index:books
where key between ["Donald Knuth", "The Art of Computer Programming", 1900]
and ["Donald Knuth", "The Art of Computer Programming", 2014]
Unsupported yet.
OrientDB since release 1.0 supports custom keys for indexes. This could give a huge improvement if you want to minimize memory used using your own serializer.
Below an example to handle SHA-256 data as binary keys without using a STRING to represent it saving disk space, cpu and memory.
public static class ComparableBinary implements Comparable<ComparableBinary> {
private byte[] value;
public ComparableBinary(byte[] buffer) {
value = buffer;
}
public int compareTo(ComparableBinary o) {
final int size = value.length;
for (int i = 0; i < size; ++i) {
if (value[i] > o.value[i])
return 1;
else if (value[i] < o.value[i])
return -1;
}
return 0;
}
public byte[] toByteArray() {
return value;
}
}
public static class OHash256Serializer implements OBinarySerializer<ComparableBinary> {
public static final OBinaryTypeSerializer INSTANCE = new OBinaryTypeSerializer();
public static final byte ID = 100;
public static final int LENGTH = 32;
public int getObjectSize(final int length) {
return length;
}
public int getObjectSize(final ComparableBinary object) {
return object.toByteArray().length;
}
public void serialize(final ComparableBinary object, final byte[] stream, final int startPosition) {
final byte[] buffer = object.toByteArray();
System.arraycopy(buffer, 0, stream, startPosition, buffer.length);
}
public ComparableBinary deserialize(final byte[] stream, final int startPosition) {
final byte[] buffer = Arrays.copyOfRange(stream, startPosition, startPosition + LENGTH);
return new ComparableBinary(buffer);
}
public int getObjectSize(byte[] stream, int startPosition) {
return LENGTH;
}
public byte getId() {
return ID;
}
}
OBinarySerializerFactory.INSTANCE.registerSerializer(new OHash256Serializer(), null);
index = database.getMetadata().getIndexManager().createIndex("custom-hash", "UNIQUE", new ORuntimeKeyIndexDefinition(OHash256Serializer.ID), null, null);
ComparableBinary key1 = new ComparableBinary(new byte[] { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 0, 1 });
ODocument doc1 = new ODocument().field("k", "key1");
index.put(key1, doc1);
Since OrientDB 1.6:
select expand(indexes) from metadata:indexmanager
Since OrientDB 1.0:
select expand(indexes) from cluster:0
Before OrientDB 1.0:
select expand(indexes) from #0:2
Here a guide how to create a custom index engine.