Indexing Embedded maps

[StevenTomer]

Can we create indexes that will efficiently index maps (both keys and
values)?

For an example of a use case, take the following:

create class Movie
create property Movie.title STRING
create property Movie.thumbs EMBEDDEDMAP SHORT

insert into movie (title, thumbs) values ('Chocolat', {'Ebert':
4,'Roeper':3})
insert into movie (title, thumbs) values ('Taken', {'Ebert':3,'Roeper':
4})
insert into movie (title, thumbs) values ('Star Trek', {'Ebert':
3,'Roeper':2})
insert into movie (title, thumbs) values ('Gone With the Wind',
{'Ebert':4})
insert into movie (title, thumbs) values ('Dark Knight', {'Roeper':4})

(Let's say there are 1 million movies, and we have many other
reviewers as well.)

I'd like to efficiently do the following types of queries:

select from movie where thumbs containskey "Ebert"
select from movie where thumbs[Roeper] = 4

Without an index, these queries are terribly slow.

Steve

[Luca Garulli]

Hi Steven,

course it's possible but it's at lower priority than other stuff. Can you open a new issue for it?

Lvc@

[Artem Orobets]

Hi,

We've implemented indexing of embedded maps. 

So, you can use following query:

• To create index by key:
  CREATE INDEX indexForMap ON classWithMap (embeddedMap by key)
  or
  CREATE INDEX indexForMap ON classWithMap ( embeddedMap )
  
• To create index by value
   CREATE INDEX indexForMap ON classWithMap (embeddedMap by value) 
  

This indexes also take part in query optimization.

Unfortunately, index can't be applied to "[]" operator, because it needs wide and deep changes in parser design, thus it can't be delivered in short term.

But you can use following way:

CREATE INDEX indexForMap ON Movie (thums  by key) UNIQUE

and then use following select query:

select * from Movie where ( thums containskey 'Ebert' ) and (  thums['Ebert'] = 12 )

Query executor optimize this query in following way

1. Fetching thumbs with such key from index
2. Applying filter to this thumbs.
   

Full description of this syntax will be described at wiki soon.

2011/12/9 Luca Garulli <l.ga...@gmail.com>

[Steven Tomer]

Is this change in the trunk?

[Luca Garulli]

Yes.

[spareshade]

sometimes old news is still a good news :)
this is bloody excellent!

i kept missing this little gem:

" select * from Movie where ( thumbs containskey 'Ebert' ) and (  thumbs['Ebert'] = 12 ) "

that is, you have to use the indexed key with containskey and then see if it has the desired value ... otherwise the index is not used

perhaps would be good to add it to documentation :) ...

[want...@gmail.com]

The index cannot be used otherwise? "SELECT FROM index: indexForMap" does not show anything, searching is completely hopeless. Only "SELECT count(*) FROM index: indexForMap" does show that there are some records.