Functional index in OrientDB

Question

I have Companies vertices in my OrientDB with property Name which represents the name of the company.

I need to search data in those vertices by property Name by paticular algorithm. For example, I have to convert all symbols to low case, remove all spaces and remove some special simbols.

In Postgresql I would be create functional index with function which implements this algorithm. But the problem is that speed of such functinal index didn't satisfy me (in table with 20 mln records search record by this index takes about 1 sec).

How can I implement such algorithm in OrientDB and how eficiently it would?

Answer 1

I used this simple dataset to try your case:

CREATE CLASS Companies EXTENDS V

CREATE PROPERTY Companies.Name STRING

CREATE INDEX Companies.Name ON Companies(Name) NOTUNIQUE_HASH_INDEX

CREATE VERTEX Companies SET Name = 'Company 1'
CREATE VERTEX Companies SET Name = 'Company 2'
CREATE VERTEX Companies SET Name = 'Company 3'
CREATE VERTEX Companies SET Name = 'Company 4'
CREATE VERTEX Companies SET Name = 'Company 5'
CREATE VERTEX Companies SET Name = 'Company 6'

1. Searching vertices by a defined algorithm:

To retrieve the results you're looking for, you can implement a query by using OrientSQL methods like .toLowerCase() and LIKE operator. E.g.:

    SELECT Name FROM Companies WHERE Name.toLowerCase() LIKE 'comp%1'

    ----+------+---------
    #   |@CLASS|Name
    ----+------+---------
    0   |null  |Company 1
    ----+------+---------

You can also create a function containing your query:

2. About the index choice, an HASH_INDEX (I used a NOTUNIQUE_HASH_INDEX) would be better because it is more practical on large numbers (like in your case) and it has many advantages as explained here

EDITED

I created this function to give an example (inputName parameter is the company Name you're looking for):

Once the function has been created, you can call it by using the OSQL:

STUDIO:

CONSOLE:

SELECT EXPAND(myFunction3('company1'))

----+-----+---------+---------
#   |@RID |@CLASS   |Name
----+-----+---------+---------
0   |#12:0|Companies|Company 1
----+-----+---------+---------

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

About the performance, I don't know the complexity of your dataset (e.g. number and type of the fields) and it's hard to say. You could test and use these examples as a starting point to create many and more complex customized functions.

Hope it helps.

Answer 2

To remove spaces try this JS function:

var g=orient.getGraph();
var c=g.command('sql','select from Companies');
var b=g.command("sql","select Name.indexOf(' ') from Companies");
var no_space=[];

for(i=0;i<c.length;i++)
{
  if(b[i].getProperty("Name")>-1)
  {
    var company=c[i].getProperty("Name").substring(0,b[i].getProperty("Name"));
    company+=c[i].getProperty("Name").substring(b[i].getProperty("Name")+1,c[i].length);
    no_space.push(company);
  }
}

return no_space;

Before

After

Hope it helps

Regards