1
Design and implement database schemas
medium
A retail company uses Cloud Spanner to store product inventory data. The table structure is:
CREATE TABLE Inventory ( ProductId INT64 NOT NULL, WarehouseId INT64 NOT NULL, StockLevel INT64 NOT NULL, LastUpdated TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp=true) ) PRIMARY KEY (ProductId, WarehouseId);
The application frequently runs the query: SELECT ProductId, SUM(StockLevel) AS TotalStock FROM Inventory WHERE WarehouseId = 123 GROUP BY ProductId. The query is slow and scans many rows. The index used is:
CREATE INDEX InventoryByWarehouse ON Inventory (WarehouseId);
What is the most effective schema change to improve query performance?