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 (email@example.com')
… 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?