Apache Geode CHANGELOG

Using Indexes with Equi-Join Queries

Equi-join queries are queries in which two regions are joined through an equality condition in the WHERE clause.

To use an index with an equi-join query:

  1. Create an index for each side of the equi-join condition. The query engine can quickly evaluate the query’s equi-join condition by iterating over the keys of the left-side and right-side indexes for an equality match.

    Note: Equi-join queries require regular indexes. Key indexes are not applied to equi-join queries.

    For this query:

    SELECT DISTINCT inv.name, ord.orderID, ord.status 
FROM /investors inv, /orders ord 
WHERE inv.investorID = ord.investorID

    Create two indexes:

    FROM clause Indexed expression
    /investors inv inv.investorID
    /orders ord ord.investorID

  2. If there are additional, single-region queries in a query with an equi-join condition, create additional indexes for the single-region conditions only if you are able to create at least one such index for each region in the query. Any indexing on a subset of the regions in the query will degrade performance.

    For this example query:

    SELECT DISTINCT *
FROM /investors inv, /securities sc, inv.heldSecurities inv_hs
    WHERE sc.status = "active"
    AND inv.name = "xyz"
    AND inv.age > 75
    AND inv_hs.secName = sc.secName

    Create the indexes for the equi-join condition:

    FROM clause Indexed expression
    /investors inv, inv.heldSecurities inv_hs inv_hs.secName
    /securities sc sc.secName

    Then, if you create any more indexes, create one on sc.status and one on inv.age or inv.name or both.