Index for joined query

SQL++
1

I have a datamodel where a user is split into two documents: User:2124DEFEC111BA8FC1257ED20034B387 for anonymous data and User:Private:2124DEFEC111BA8FC1257ED20034B387 for all the personal information…

So if I have the key of the user I can do a quick lookup for the private information. And in my Java code I have a class that works with the entire User object. To retrieve that I can use a query like this:

SELECT t2.*, t1.* FROM data AS t1 
LEFT JOIN data AS t2 ON 'User:Private:' || t1.`key` = META(t2).id AND t2.type='Private' 
WHERE t1.type='User'

That is all good. However, when I want to find a user using the email (that is private information) I use this query:

SELECT t2.*, t1.* FROM data AS t1 
LEFT JOIN data AS t2 ON 'User:Private:' || t1.`key` = META(t2).id AND t2.type='Private' 
WHERE t1.type='User' AND (t2.email='john@dalsgaard-data.dk')

… but it is slow (2-4 secs.) as it does not use any of the indexes that I have tried.

I have a type field on both documents: User for the first and Private for the second. They both have a key field with the same value.

How should I build an index to speed up this search?

2

Move the selective predicate to left side.

CREATE INDEX ix1 ON data(email) WHERE type = "Private";
SELECT t2.*, t1.*
FROM data AS t2
LEFT JOIN data AS t1 ON 'User:' || t2.`key` = META(t1).id AND t1.type='User'
WHERE t2.type='Private' AND (t2.email='john@dalsgaard-data.dk')
3

Ahhh… very elegant!!! Thank you!