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?