Index Types Reference

CREATE INDEX [index_name] ON :Label(property) USING BTREE;
CREATE INDEX [index_name] ON :Label(property);  -- BTREE is default

CREATE INDEX person_age_idx ON :Person(age) USING BTREE
  OPTIONS {
    fill_factor: 0.9,        -- Page fill factor (default: 0.9)
    unique: false,           -- Enforce uniqueness
    nulls_distinct: true     -- Treat NULLs as distinct values
  };

Operation          | Time Complexity | Notes
-------------------|-----------------|----------------------------------
Point lookup       | O(log N)        | Tree traversal
Range scan         | O(log N + K)    | K = result count
Insert             | O(log N)        | May trigger page split
Delete             | O(log N)        | May trigger page merge
Bulk load          | O(N log N)      | Sequential insert optimization

-- Range query with ordering
CREATE INDEX person_age_idx ON :Person(age) USING BTREE;

MATCH (p:Person)
WHERE p.age >= 25 AND p.age <= 65
RETURN p.name, p.age
ORDER BY p.age DESC;

-- Prefix search
CREATE INDEX person_name_idx ON :Person(name) USING BTREE;

MATCH (p:Person)
WHERE p.name STARTS WITH 'Alex'
RETURN p.name;

-- Unique constraint
CREATE INDEX person_email_unique ON :Person(email) USING BTREE
  OPTIONS { unique: true };

CREATE INDEX [index_name] ON :Label(property) USING HASH;

CREATE INDEX user_id_idx ON :User(id) USING HASH
  OPTIONS {
    bucket_count: 1024,      -- Initial hash buckets
    load_factor: 0.75        -- Resize threshold
  };

Operation          | Time Complexity | Notes
-------------------|-----------------|----------------------------------
Point lookup       | O(1) average    | O(N) worst case with collisions
Insert             | O(1) average    | May trigger rehash
Delete             | O(1) average    | Tombstone-based
Bulk load          | O(N)            | Linear time

-- High-cardinality equality lookup
CREATE INDEX user_email_idx ON :User(email) USING HASH;

MATCH (u:User)
WHERE u.email = 'alice@example.com'
RETURN u;

-- UUID lookups
CREATE INDEX session_id_idx ON :Session(id) USING HASH;

MATCH (s:Session)
WHERE s.id = '550e8400-e29b-41d4-a716-446655440000'
RETURN s;

CREATE INDEX [index_name] ON :Label(property) USING FULLTEXT;

CREATE INDEX article_content_idx ON :Article(content) USING FULLTEXT
  OPTIONS {
    language: 'english',     -- Stemming language
    min_word_length: 3,      -- Minimum word length
    stop_words: 'default',   -- Stop word list
    k1: 1.2,                 -- BM25 term frequency saturation
    b: 0.75                  -- BM25 length normalization
  };

BM25(d, q) = SUM_i [ IDF(qi) * f(qi,d) * (k1+1) / (f(qi,d) + k1*(1-b+b*|d|/avgdl)) ]

Where:
- f(qi,d) = frequency of term qi in document d
- |d| = document length (word count)
- avgdl = average document length
- k1 = term frequency saturation (higher = more weight to frequency)
- b = length normalization (higher = penalize long documents more)

-- Basic full-text search
CREATE INDEX doc_content_idx ON :Document(content) USING FULLTEXT;

CALL geode.fts.search('document_content', 'graph database performance')
YIELD node, score
MATCH (d:Document) WHERE id(d) = node
RETURN d.title, score AS relevance
ORDER BY relevance DESC
LIMIT 10;

-- Boolean operators
CALL geode.fts.search('document_content', 'graph AND (database OR storage) NOT sql')
YIELD node, score
MATCH (d:Document) WHERE id(d) = node
RETURN d.title;

-- Phrase search
CALL geode.fts.search('document_content', '"property graph"')
YIELD node, score
MATCH (d:Document) WHERE id(d) = node
RETURN d.title;

-- Fuzzy search (typo-tolerant)
CALL geode.fts.search('document_content', 'databse~')
YIELD node, score
MATCH (d:Document)
-- Matches "database"
WHERE id(d) = node
RETURN d.title;

CREATE INDEX [index_name] ON :Label(property) USING SPATIAL;

CREATE INDEX location_idx ON :Store(location) USING SPATIAL
  OPTIONS {
    srid: 4326,              -- Spatial Reference ID (WGS84)
    precision: 'high'        -- Index precision
  };

Operation          | Time Complexity | Notes
-------------------|-----------------|----------------------------------
Point-in-region    | O(log N)        | R-tree traversal
k-nearest          | O(log N + K)    | Priority queue search
Range query        | O(log N + K)    | Bounding box filter
Insert             | O(log N)        | May trigger split

-- Find stores within 5km
CREATE INDEX store_location_idx ON :Store(location) USING SPATIAL;

MATCH (s:Store)
WHERE st_distance(s.location, latlon(40.7128, -74.0060)) < 5000
RETURN s.name, st_distance(s.location, latlon(40.7128, -74.0060)) AS meters
ORDER BY meters
LIMIT 10;

-- Bounding box query
MATCH (s:Store)
WHERE geobox_contains(s.location,
                     latlon(40.70, -74.05),   -- Southwest corner
                     latlon(40.75, -73.95))   -- Northeast corner
RETURN s.name;

-- Polygon containment
WITH geometryfromtext([
  latlon(40.70, -74.05),
  latlon(40.70, -73.95),
  latlon(40.75, -73.95),
  latlon(40.75, -74.05)
]) AS nyc_area
MATCH (s:Store)
WHERE st_within(s.location, nyc_area)
RETURN s.name;

CREATE INDEX [index_name] ON :Label(property) USING VECTOR;

CREATE INDEX embedding_idx ON :Document(embedding) USING VECTOR
  WITH (metric = 'l2');

Operation          | Time Complexity | Notes
-------------------|-----------------|----------------------------------
k-NN search        | O(log N)        | Approximate, SIMD-accelerated
Insert             | O(log N)        | Builds graph structure
Delete             | O(log N)        | Marks as deleted (lazy cleanup)
Build              | O(N log N)      | Initial index construction

-- Create vector index
CREATE INDEX doc_embedding_idx ON :Document(embedding) USING VECTOR
  WITH (metric = 'cosine');

-- k-NN similarity search
MATCH (d:Document)
WITH d, distance(d.embedding, $query_vector, 'cosine') AS distance
WHERE distance < 0.5
RETURN d.title, distance
ORDER BY distance
LIMIT 10;

-- Find similar documents to a given document (ranked by similarity, highest first)
MATCH (source:Document {id: 123})
MATCH (d:Document)
WHERE d.id <> source.id
WITH d, similarity(d.embedding, source.embedding) AS similarity
RETURN d.title, similarity
ORDER BY similarity DESC
LIMIT 5;

-- Hybrid search: vector + filter
MATCH (d:Document)
WHERE d.category = 'technical'
WITH d, vector_l2(d.embedding, $query_vector) AS distance
RETURN d.title, distance
ORDER BY distance
LIMIT 10;

CREATE INDEX [index_name] ON :Label(prop1, prop2, ...) USING BTREE;

-- Index on (lastname, firstname)
CREATE INDEX person_name_idx ON :Person(lastname, firstname);

-- Can use index:
WHERE p.lastname = 'Smith'                           -- Leading column
WHERE p.lastname = 'Smith' AND p.firstname = 'John' -- Both columns

-- Cannot use index efficiently:
WHERE p.firstname = 'John'                           -- Non-leading column only

-- Composite index for common query pattern
CREATE INDEX order_customer_date_idx ON :Order(customer_id, order_date);

-- Query using both columns (optimal)
MATCH (o:Order)
WHERE o.customer_id = 123 AND o.order_date >= date('2026-01-01')
RETURN o;

-- Query using leading column only (still uses index)
MATCH (o:Order)
WHERE o.customer_id = 123
RETURN o;

-- JSONB path queries use inverted index
MATCH (d:Document)
WHERE d.metadata->'author' = '"Alice"'
RETURN d;

MATCH (d:Document)
WHERE d.metadata->'tags'->0 = '"ml"'
RETURN d;

-- Interval overlap queries
MATCH (e:Event)
WHERE e.duration OVERLAPS interval('[2026-01-01, 2026-01-31]')
RETURN e;

-- IP subnet containment
MATCH (s:Server)
WHERE ip_contains('10.0.0.0/8'::subnet, s.ip)
RETURN s;

-- Show all indexes
SHOW INDEXES;

-- Show indexes for a label
SHOW INDEXES ON :Person;

-- Show index details
SHOW INDEX person_email_idx;

-- Drop by name
DROP INDEX person_email_idx;

-- Drop with IF EXISTS
DROP INDEX IF EXISTS person_email_idx;

-- List every index with its type, label and properties
SHOW INDEXES;

-- Same list through the introspection procedure
CALL db.indexes() YIELD name, type, label, properties
RETURN name, type, label, properties
ORDER BY name;

DROP INDEX person_email_idx;
CREATE INDEX person_email_idx ON Person(email);

EXPLAIN MATCH (p:Person) WHERE p.email = 'alice@example.com' RETURN p;

-- Check if index is being used
EXPLAIN
MATCH (p:Person)
WHERE p.email = 'alice@example.com'
RETURN p;

-- Look for "uses index: <name>" in output

-- Composite index covers the query
CREATE INDEX person_name_email_idx ON :Person(name, email);

-- Query returns indexed columns only (no table lookup)
MATCH (p:Person)
WHERE p.name = 'Alice'
RETURN p.email;