Graph Data Modeling

Composite index vs full-text index selection

You have a query that filters on more than one property, and you need to decide whether a composite RANGE index or a FULLTEXT index will serve it — a choice developers routinely get wrong because both look like “an index over several properties.” They solve opposite problems. A composite index answers exact and range predicates joined by AND, keeps results ordered, and is cheap to maintain; a FULLTEXT index tokenises text into a Lucene inverted structure so it can answer substring, fuzzy, and relevance-ranked search that a composite index cannot touch. This page draws the boundary precisely, shows the CREATE statement and the query each index actually serves, and proves the difference with PROFILE. It is part of index selection and composite indexing.

Prerequisites

The distinction in one sentence

A composite RANGE index serves a known, structured predicate — the value is compared by equality or range and the index preserves order — whereas a FULLTEXT index serves an open, textual predicate — the value is a fragment, a misspelling, or a term that may appear anywhere inside a longer string. If the user types the whole value and you compare it with =, >=, or STARTS WITH, that is composite territory. If the user types part of a word and expects matches anywhere in the text, or expects the closest match rather than an exact one, that is FULLTEXT territory.

Composite RANGE index versus FULLTEXT index, side by side Two panels. The left panel, headed Composite RANGE index, serves predicates of the form status equals a value AND processed_at greater than or equal to a timestamp; it preserves ordering, resolves in a single NodeIndexSeek, and has low write cost. The right panel, headed FULLTEXT index, serves predicates like a substring, a fuzzy misspelling, or a multi-word term appearing anywhere in the text; it returns a relevance score through db.index.fulltext.queryNodes and has higher write cost because it tokenises on every write. Composite RANGE index serves STRUCTURED predicates t.status = $s AND t.ts >= $from exact equality · range · STARTS WITH ✓ preserves ORDER BY, no Sort operator ✓ single NodeIndexSeek ✓ low write-maintenance cost ✗ cannot do mid-string CONTAINS NodeIndexSeek rows ≈ matched, few db-hits FULLTEXT index serves OPEN text predicates "acme~" · "*logistics*" · fuzzy substring · misspelling · multi-field ✓ tokenised, matches anywhere in text ✓ returns a relevance score ✗ no range / ORDER BY guarantee ✗ higher write cost (tokenises) queryNodes (score) ranked hits from Lucene
Structured equality and range go left; open, tokenised text search goes right. They are not interchangeable.

Core implementation

Take one label, Customer, with a status enum, a signup_at timestamp, and a free-text display_name. Two different queries need two different indexes.

cypher
// CASE A — structured predicate: filter active customers who signed up in a window,
// newest first. Equality on status, range on signup_at, ordered output.
// A COMPOSITE RANGE index resolves both predicates in one seek AND removes the Sort.
CREATE INDEX customer_status_signup IF NOT EXISTS
FOR (c:Customer) ON (c.status, c.signup_at);

MATCH (c:Customer)
WHERE c.status = $status AND c.signup_at >= $from
RETURN c.customer_id, c.display_name, c.signup_at
ORDER BY c.signup_at DESC
LIMIT 50;
cypher
// CASE B — open text predicate: the user typed "acme" and expects any customer
// whose name CONTAINS it, or a near miss like "acme~" (fuzzy). A composite index
// CANNOT serve this; only a FULLTEXT (Lucene) index tokenises the name.
CREATE FULLTEXT INDEX customer_name_search IF NOT EXISTS
FOR (c:Customer) ON EACH [c.display_name];

// Query the FULLTEXT index by procedure — it returns a relevance score, not a seek.
CALL db.index.fulltext.queryNodes('customer_name_search', $term)
YIELD node, score
RETURN node.customer_id, node.display_name, score
ORDER BY score DESC
LIMIT 50;

The load-bearing distinction is in the predicate, not the number of properties. Case A joins two structured comparisons with AND; because the index key is (status, signup_at) and the query filters equality on the leading property and a range on the trailing one, the planner resolves it as a single NodeIndexSeek and the ordered leaves satisfy ORDER BY without a Sort. Case B asks “does this fragment appear anywhere in the name” — a question a sorted key structure cannot answer, because the value the user supplied is not a prefix of the stored string. Only the tokenised inverted index matches mid-string and ranks by relevance.

Validation & verification

Prove each index is doing its job with PROFILE. For the composite case, the leaf must be a NodeIndexSeek over customer_status_signup, and no Sort should appear.

cypher
PROFILE
MATCH (c:Customer)
WHERE c.status = $status AND c.signup_at >= $from
RETURN c.customer_id
ORDER BY c.signup_at DESC
LIMIT 50;
// Expect: NodeIndexSeek (customer_status_signup), no Sort, db-hits ~ rows returned.

For the full-text case, confirm the procedure hits the index rather than a scan, and that a bare CONTAINS against the same data does not use the composite index:

cypher
// A mid-string CONTAINS cannot seek a RANGE/composite index — this scans.
PROFILE
MATCH (c:Customer)
WHERE c.display_name CONTAINS $fragment
RETURN c.customer_id;
// Expect: NodeByLabelScan + Filter — the evidence you need FULLTEXT instead.

If the CONTAINS plan shows a scan while the queryNodes plan resolves against the Lucene index, you have positive proof the two indexes are not substitutes. Confirm both indexes are ONLINE first with SHOW INDEXES YIELD name, type, state.

Edge cases & gotchas

1. STARTS WITH is composite territory, CONTAINS is not. A prefix search resolves against a RANGE or composite index because the supplied value is a prefix of the key. Only mid-string CONTAINS, suffix ENDS WITH, or fuzzy matching forces FULLTEXT.

cypher
// Prefix — uses the RANGE/composite index (seek on a key prefix):
WHERE c.display_name STARTS WITH $prefix
// Substring — degenerates to a scan; needs a FULLTEXT index instead:
WHERE c.display_name CONTAINS $fragment

2. Escaping and analyzers change what matches. The full-text query string is Lucene syntax, so reserved characters (+ - && || ! ( ) { } [ ] ^ " ~ * ? : \) must be escaped or they alter the query. Configure the analyzer at creation for language-appropriate tokenisation; the default splits on whitespace and lowercases.

3. Do not FULLTEXT-index a property a composite already serves. A FULLTEXT index tokenises on every write and is the most expensive index to maintain. If every query against the property is exact or range, keep the composite index and drop the FULLTEXT one — creating both taxes writes for a capability you do not use.

Related pages