Oh! That’s makes sense. The order you have then makes sense. step 1, filter all the possible values. step 2, sort by the random number.
I’m guessing that
all_names index is still probably contributing to the bottleneck. Here’s some ideas, hopefully applicable to the larger query. They work on the idea to combine the smallest sets and fewest operations possible.
tip: try to reduce the size of the first set that is a part of the Diff.
This would be equivalent to:
Depending on the ratio of true to false in the “top” then this might be really helpful. Or only sort out like 10 records lol
If there are any filters subsequent to this that are always run and filter significant users, perhaps that can be pushed to the top of the
tip: For that matter, if there are multiple filters that are ALWAYS applied, put them all in a single index.
If it’s possible to avoid
Intersection with a single index, do it.
For example, if gender and origin are ALWAYS applied as filters, then put that index at the top of the diff.
// start with all always-on filters
q.Match(q.Index("names_by_base_filters"), true, Var('gender'), Var('origin'), /* and others?*/),
q.Match(q.Index("names_by_custom"), true), //exclude all custom names, except:
q.Match(q.Index("custom_names_by_user"), q.Var('userRef')) //1. custom names from user
/* ... other filter queries */
tip: order the
Difference arguments such that they will filter out the most documents sooner.
It’s my intuition that this will work, but I’ve not verified. I’d say at least worth testing. I don’t know if doing so will be much of an impact on your query though, based on what you’ve shared.
tip: use conditional logic to reduce
Again, don’t know if it’s applicable.
But if you can remove a single
Difference argument by checking some condition, then maybe you can optimize the query.
If(Equals(Select(['data', 'something'] , Var('userDocument')), 'some_value'), 'Intersect_THIS', 'Intersect_THAT')
This can help avoid working over “all” Sets.
Since Set operations like
Union, etc. typically also work on arrays, you can do some interesting things to build an array of
SetRefs conditionally and the pass that to the Set operation.
tip: denormalize/ duplicate data.
This one makes me kinda sad, because FQL is great for working out complex queries without duplicating data. But if you REALLY REALLY need to optimize reads more, and writing isn’t so bad, then maybe there’s an option here.