CODEWITHSUNDEEP
google-cloud-platformgoogle-cloud-spanner
Chipintoza
Chipintoza

Reputation: 295

Understanding Secondary Indexes

if i have Table Table

CREATE TABLE Users (
    userId STRING(36) NOT NULL,
    contactName STRING(300) NOT NULL,
    eMail STRING(100) NOT NULL,
    ....
) PRIMARY KEY (userId)

and secondary index

CREATE NULL_FILTERED INDEX ActiveUsersByEMail 
ON Users (
    eMail,
    isActive,
)

and i select record by:

SELECT * FROM Users WHERE eMail = '[email protected]' AND isActive = TRUE

spanner will automatically look at index, take userId and give me a record ?.

or i need to create

CREATE NULL_FILTERED INDEX ActiveUsersByEMail_01 
ON Users (
    eMail,
    isActive,
    userId
)

and first take userId by:

SELECT userId from Users@{FORCE_INDEX=ActiveUsersByEMail_01} WHERE eMail = '[email protected]' AND isActive = TRUE

and then i take a record by:

`SELECT * FROM Users WHERE userId = '${userId}'``

Question is automatically use or not spanner secondary indices for standard select if condition match secondary index keys?

Upvotes: 2

Views: 657

Answers (2)

Chipintoza
Chipintoza

Reputation: 295

In github i ask same question and It turned out that this is easily done (without creating additional index) by:

SELECT * from Users@{FORCE_INDEX=ActiveUsersByEMail} WHERE eMail = '[email protected]' AND isActive = TRUE

At this time the search is going on index and row come with all fields

Upvotes: 0

Albert Cui
Albert Cui

Reputation: 394

You should use FORCE_INDEX as Cloud Spanner will only choose an index in rare circumstances as stated here. You can use the STORING clause to add data directly to the index, allowing you to read the data directly from the index to avoid the second call. This is suggested for common query patterns in your application.

Upvotes: 2

Related Questions