If I run the following query:
SELECT meta().id, nickName FROM Account AS x WHERE x.type='user' AND x.email LIKE 'example%' UNION ALL SELECT meta().id, nickName FROM Account AS x WHERE x.type='userList' ORDER BY nickName ASC
With two indexes in the system:
CREATE INDEX UserIndex ON Account(type,nickName,email) WHERE (type = "user") CREATE INDEX UserListIndex ON Account(type,nickName) WHERE (type = "userList")
The second part of the query is not covered by the UserListIndex (the EXPLAIN has a fetch step).
However, If i remove the line starting with “AND”, or change it to use the nickName field, it then is covered.
Or, if I instead only remove the ORDER BY clause, it is also covered in that case.
Something seems a little fishy here…