Indexing with the Index service (and legacy Views)

-- Every document ID in the keyspace; no predicate needed to build it.
CREATE PRIMARY INDEX ON `travel-sample`.inventory.airline USING GSI;

-- It lets an unplanned, ad-hoc query run at all:
SELECT * FROM `travel-sample`.inventory.airline WHERE callsign = "UNITED";
-- With no primary index and no other usable index this fails with
--   "No index available on keyspace ... that matches your query"

-- Single-field secondary index.
CREATE INDEX idx_city ON `travel-sample`.inventory.hotel(city);

-- Composite (compound) index: the leading key must be usable by the predicate.
CREATE INDEX idx_city_name ON `travel-sample`.inventory.hotel(city, name);

-- Serves:  WHERE city = ?          and  WHERE city = ? AND name = ?
-- Does not serve:  WHERE name = ?   (name is not a leading key)

-- Index only the documents that match the index WHERE clause.
CREATE INDEX idx_open_orders ON orders(customerId) WHERE status = "OPEN";

SELECT * FROM orders WHERE customerId = 42 AND status = "OPEN";  -- uses idx_open_orders
SELECT * FROM orders WHERE customerId = 42;                      -- cannot: filter not implied

-- Index each element of an array with DISTINCT ARRAY ... FOR ... IN ... END
CREATE INDEX idx_sched_day ON `travel-sample`.inventory.route
  (DISTINCT ARRAY s.day FOR s IN schedule END);

-- The ANY ... SATISFIES predicate is now an index scan, not a keyspace scan:
SELECT META().id FROM `travel-sample`.inventory.route
WHERE ANY s IN schedule SATISFIES s.day = 2 END;

CREATE INDEX idx_sched ON `travel-sample`.inventory.route
  (ALL ARRAY FLATTEN_KEYS(s.day, s.utc) FOR s IN schedule END, sourceairport);

-- One index that covers equality predicates on ANY field...
CREATE INDEX idx_adaptive ON `travel-sample`.inventory.hotel(DISTINCT PAIRS(SELF));

-- ...or a named subset of fields:
CREATE INDEX idx_adaptive_sub ON `travel-sample`.inventory.hotel
  (DISTINCT PAIRS({city, country, state, type}));

-- If every field the query reads is in the index key, the Data-service fetch is skipped.
CREATE INDEX idx_cover ON `travel-sample`.inventory.hotel(city, name, country);

EXPLAIN SELECT name, country FROM `travel-sample`.inventory.hotel WHERE city = "Paris";
-- plan shows an IndexScan3 with a "covers" array and NO Fetch operator

-- Hash-partition one index across Index nodes; keep one extra copy of each partition.
CREATE INDEX idx_part ON orders(customerId, orderDate)
  PARTITION BY HASH(customerId)
  WITH { "num_partition": 8, "num_replica": 1 };

-- Create several indexes cheaply, deferring the scan that populates them.
CREATE INDEX idx_a ON orders(a) WITH { "defer_build": true };
CREATE INDEX idx_b ON orders(b) WITH { "defer_build": true };

-- Then build them together in a single pass over the keyspace:
BUILD INDEX ON orders(idx_a, idx_b) USING GSI;

-- EXPLAIN: print the plan without running the statement.
EXPLAIN SELECT name FROM `travel-sample`.inventory.hotel WHERE city = "Paris";

-- ADVISE: ask the Index Advisor which indexes would help this statement.
ADVISE SELECT name FROM `travel-sample`.inventory.hotel
WHERE city = "Paris" AND country = "France";
-- => recommended: CREATE INDEX adv_city_country ON ...(`city`, `country`)

-- Collect distribution statistics so the CBO can cost candidate plans.
UPDATE STATISTICS FOR `travel-sample`.inventory.hotel(city, country, type);

-- ANALYZE is an accepted synonym:
ANALYZE KEYSPACE `travel-sample`.inventory.hotel(city, country);

EXPLAIN SELECT META().id FROM orders WHERE status = "OPEN";