REFRESH SPATIAL INDEX

TiDB Cloud Lake automatically refreshes spatial indexes in SYNC mode whenever new data is written. Use REFRESH SPATIAL INDEX primarily to backfill rows that existed before the index was declared.

Syntax

REFRESH SPATIAL INDEX <index> ON [<database>.]<table> [LIMIT <limit>]

Examples

-- Existing table with data loaded before the index was declared
CREATE TABLE IF NOT EXISTS stores (
  store_id INT,
  location GEOMETRY
) ENGINE = FUSE;

INSERT INTO stores VALUES
  (1, TO_GEOMETRY('POINT(10 10)')),
  (2, TO_GEOMETRY('POINT(20 20)'));

-- Create the spatial index afterward
CREATE SPATIAL INDEX stores_location_idx ON stores(location);

-- Backfill historical rows so the index covers earlier inserts
REFRESH SPATIAL INDEX stores_location_idx ON stores;

-- Future inserts refresh automatically in SYNC mode