Per my knowledge, SQL2 query optimization follows more or less similar best practices as SQL. You may search for 'SQL tuning best practices' and implement the same.
This query is already using "CONTAINS" and specific node (nt:unstructured) to be searched which is good.
Couple of things may be revisited (and tested) accordingly:
-Using select fields rather than select *
-avoiding wild cards in the front
-use UNION than ORs
-use inner joins
https://www.sisense.com/blog/8-ways-fine-tune-sql-queries-production-databases/
If you use OOB Query Performance tool (http://localhost:4502/libs/granite/operations/content/diagnosistools/queryPerformance.html), it would provide you with optimized query. In your case, the optimized query follows same SQL standards.
The cost can be worked upon using indexes.
Attempting optimisation
Alternative query available:
select [p].[jcr:primaryType] as [p.jcr:primaryType] from [nt:unstructured] as [p] where (contains([p].[familyName], '*%s*')) and (isdescendantnode([p], [/home/users])) and ([p].[email] is not null) and ([p].[id] is not null) union
select [p].[jcr:primaryType] as [p.jcr:primaryType] from [nt:unstructured] as [p] where (contains([p].[email], '*%s*')) and (isdescendantnode([p], [/home/users])) and ([p].[email] is not null) and ([p].[id] is not null) union
select [p].[jcr:primaryType] as [p.jcr:primaryType] from [nt:unstructured] as [p] where (contains([p].[preferredName], '*%s*')) and (isdescendantnode([p], [/home/users])) and ([p].[email] is not null) and ([p].[id] is not null) union
select [p].[jcr:primaryType] as [p.jcr:primaryType] from [nt:unstructured] as [p] where (contains([p].[givenName], '*%s*')) and (isdescendantnode([p], [/home/users])) and ([p].[email] is not null) and ([p].[id] is not null)
