Choosing Indexes for Category and Price Filters
A marketplace graph has 40 million :Product nodes and 1.5 million :Category nodes. Each product is attached to one category by an :IN_CATEGORY relationship. The largest category contains 900,000 products. Query A is MATCH (p:Product)-[:IN_CATEGORY]->(c:Category {slug:$slug}) WHERE p.price >= $min AND p.price < $max RETURN p ORDER BY p.price LIMIT 50; its p95 is 4.8 seconds. Query B is MATCH (p:Product) WHERE toLower(p.name) CONTAINS toLower($q) RETURN p LIMIT 20; its p95 is 12 seconds. Existing schema has a uniqueness constraint on :Product(sku) and a range index on :Category(slug). Product names average 60 characters, and price ranges typically return 2,000 to 40,000 products in a large category. The team wants to add indexes without denormalizing the model, if possible, and without changing application query semantics.
1. What index would you add for Query A, and why is an index on :Product(category, price) not directly available in this model? Explain how the planner can use your index with ORDER BY and LIMIT.
Read the worked answer
The category is not a property of :Product; it is a node reached by a relationship. A composite range index can only cover properties on the same entity, so :Product(category, price) would require denormalizing the category slug onto Product, and maintaining it if products move. Without that, add a range index on :Product(price). The planner has two candidate shapes: start at :Category(slug), expand 900k :IN_CATEGORY relationships, filter price, sort, and take 50; or scan :Product(price) in ascending order, traverse to :Category, filter slug, and stop after 50 matches. The second can avoid a large sort if the price range is selective and the category is common, but it may traverse many products if the category is rare. Compare both with PROFILE. If the category-first plan still wins, denormalizing categorySlug and creating :Product(categorySlug, price) is the index that directly serves equality plus range plus order.
2. Would you use a range index, text index, or full-text index for Query B? Address the toLower() function and the difference between substring and token search.
Read the worked answer
A range index cannot serve CONTAINS with a leading wildcard, and toLower(p.name) prevents use of an index on p.name because the indexed value is not the function result. If the requirement is literal substring matching, store a normalized nameLower property and create a text index on :Product(nameLower); query p.nameLower CONTAINS $qLower. A text index supports CONTAINS, STARTS WITH, and ENDS WITH, but it is not a relevance-ranked token search. If users expect word or token search, fuzzy matching, or phrase scoring, use a full-text index on :Product(name) and db.index.fulltext.queryNodes. Full-text does not make arbitrary middle-of-word substring search efficient unless you add wildcards, which has its own cost. Also keep the LIMIT: without an ORDER BY, Neo4j can stop after 20 matches, but with relevance ordering it must score matches.
3. How would you verify the new indexes are actually used and that Query A improved for both a large and a small category?
Read the worked answer
Check SHOW INDEXES for state ONLINE and population 100%. Run PROFILE with parameters for one large category and one small category; confirm the plan uses the intended index operator and record db hits and rows at the expand and sort. Compare p95 with the same parameters and a warm cache; a single fast run is not enough. For Query A, verify the first 50 rows are correct and ordered; if the plan uses the price index, check that it stops early rather than scanning all 40M products. For Query B, confirm the text index is used and that nameLower is populated for existing and new products; backfill in batches before switching reads. Finally, test write cost: each additional index slows product inserts and updates.