Querying and filtering best practice

Help
,
3

Hi @perestrelo and welcome to the forums.

Excellent answer, @TriangularCube!

Equality and Inequality

You can generally differentiate the way indexes are used by considering if you want to search for equality or for inequality.

  • searching for equality (e.g. WHERE A = B):
    • use Index terms to match exactly
  • searching for inequality (e.g. WHERE A < B or WHERE A >= B, etc.):
    • use Index values and the Range function

Use Singleton to change the shape of a Set

You saw that using different values for different Indexes means that they cannot be used directly by Intersection.

@TriangularCube showed the use of an Index takes a ref and returns a ref. This does work, but it can be simplified a bit with the use of Singleton. Doing so is recommended because it will reduce Read Ops and perform better. Writes and storage may also be cheaper without the additional index.

Paginate(
    Intersection(
      Join(
        Range(Match(Index('client_by_sal')), [2000], []),
        Lambda(['value', 'ref'], Singleton(Var('ref')))
      )),
      Join(
        Range(Match(Index('client_by_age')), [20], []),
        Lambda(['value', 'ref'], Singleton(Var('ref')))
      ))
    )
)
1 Like