Create index for different projects


#1

Hi,

I’m developing a project that is likely to end up having a large number of separate sub-projects, each with its own data.

I’m looking at the CREATE INDEX syntax, and thinking that using something like:

CREATE INDEX my_index ON my_bucket ( … ) WHERE _pid = 10

for example might be a way to go to create indexes just on the project wtih an ID of 10.

What I’m wondering is whether the correct index will always be selected if _pid = 10 is provided. My assumption is that it should be, I just wanted to ask.

Thanks,

Marcus.


#2

Hi Marcus,

you are correct; if you have two indexes defined:
CREATE INDEX idx1 ON my_bucket(field1)
CREATE INDEX idx2 ON my_bucket(field1) WHERE _pid = 10

Then the query: SELECT f1, f2, f3 FROM my_bucket WHERE field1 = some_value
would use index idx1

And the query: SELECT f1, f2, f3 FROM my_bucket WHERE field1 = some_value AND _pid = 10
would use index idx2

You can use EXPLAIN to verify what indexes a query will use, eg.
EXPLAIN SELECT f1, f2, f3 FROM my_bucket WHERE field1 = some_value AND _pid = 10


#3

@colm

Thanks for the confirmation.