Query Execution and Performance

When executing a join that involves queries (for example, using join()), LogScale executes the overall query inside out. That is, the subquery is executed first in order to create the event dataset that is then used to match against the primary query.

For best performance, the subquery of the join operation should be significantly smaller than the primary query. This can be archived by doing a lot of filtering in the subquery to restrict the search results. With a smaller subquery, the number of lookups and matches against the query on the left will be reduced resulting in better performance.

Negating Joins

Joins can be negated, so that the query explicitly returns the rows that dont create a valid match. For example, when performing the inner join using the weblog and authentication repositories, the result did not include the weblgo entry that was missing a authenticated user. To get the rows that dont provide a match, use a negated join:

type=weblog
| !join({type="authentication"},field=authuser,key=user,mode=inner)

This creates an result set that includes weblog entries without a valid authenticated user:

Debugging Inner Joins

When debugging an inner join query, temporarily set the join mode to left (join(mode=left)). The query will then output the full query set including rows that do not match the inner join clause.

Joins and Live Queries

Using joins in live queries is not recommended. When a join is executed, each query pair (primary and subquery) are executed indepedently. The results may not be computed consistently across the two queries, with the queries are executed against the data being ingested which may have changed between executing each query.

Because the datasets from the primary query and subquery are inconsistent, the results of the join are also inconsistent.

The result is that the two queries are run repeatedly rather than being executed as a typical live query which will be less efficient that within a typical live query execution.

This execution style is different to a typical SQL database, where the set of rows in the query will be configured to be in repeatable read, so the set of rows is consistent each time.

For this reason, using join() within an alert is not recommended. A warning will be produced if LogScale identifies that a query contains a join.