What is the most efficient way to find parents without childs on Google Spanner interleaved tables?

Gabriel Mendes 0
Viewed 18

I have two very big tables interleaved by its primary key (just one column, so it is one-to-one relationship). A few rows on the parent table have no child on the other and I want to find them.

Currently, I am doing a JOIN query and searching by NULL values:

         SELECT Users.userID
            FROM Users
            LEFT JOIN Licenses
            ON Users.userID = Licenses.userID
            WHERE Licenses.license IS NULL

But this query still needs to read all Users table to do the JOIN, what is really slow.

I know that if the license column was in the table Users I could create an index with it and would only need to read the rows with NULL license values, but it is not an option to put the column "license" in the same "Users" table.

Is there a way to just pass through the userIDs that do not have yet a license using different tables? e.g. an index with columns from different tables. (I am using interleaved but would it be better foreign keys?)

interleave gql google-cloud-spanner foreign-keys performance
1 Answers

To clarify, is the following the correct understanding?

  • Users table has primary key UserId
  • Licenses table has primary key UserId, and license column
    • A UserId can only be associated with 0 or 1 licenses
    • Licenses table will only have a row when Licenses.license != NULL, so the number of rows in Licenses < number of rows in Users
  • Both Users and Licenses tables contain many rows

In order to avoid scanning the Users table, perhaps the Licenses table can include even UserIds without licenses. Then, an Index on Licenses.license can be created to help search for licenses that are NULL.

Alternatively, the application can track additional state when inserting/deleting UserIds in Users and inserting/deleting licenses in Licenses. For example, keep track of a MissingLicense table containing all UserIds without a license. The MissingLicense table would have a primary key UserId. When inserting a UserId without a license, insert into MissingLicense as well. When inserting a license for a user, delete from MissingLicense. When deleting a UserId from Users, delete from MissingLicense. MissingLicense can be interleaved under the Users table with ON DELETE CASCADE.

Rose Liu0
Related