Understanding Compound Indexes

Welcome back to Indexes and Performances! You're now on lesson three, building on the indexing fundamentals we covered previously. In the last lesson, we learned how to create indexes on individual properties like name or email. But what happens when your queries frequently filter on multiple properties together? For example, finding users in a specific city who are above a certain age, or locating orders from a particular customer within a date range.

Creating separate indexes for each property might not be enough. In this lesson, we'll explore compound indexes, also known as composite indexes, which allow Neo4j to optimize queries that filter on multiple properties simultaneously. We'll learn when they're beneficial, how to create them, and understand the important role that property order plays in their effectiveness.

A compound index is an index built on multiple properties of the same node or relationship type, rather than just one. Think of it like an organized filing system that sorts documents first by department, then by date within each department. This dual-level organization lets you quickly find specific combinations, such as all documents from the engineering department created in March.

Similarly, a compound index on (city, age) creates a structure that organizes nodes first by city, then by age within each city. This structure becomes powerful when queries need to filter on both properties because Neo4j can navigate directly to the relevant subset of data without examining nodes that don't match either criterion. The database maintains this multi-property structure automatically as data changes, keeping it synchronized with your graph.

Compound indexes shine in specific scenarios where your query patterns are predictable and consistent. The most obvious case is when you frequently filter on the same combination of properties together. For instance, if your application regularly searches for users by combining location and demographic filters, or finds products by category and price range, these repeated patterns make excellent candidates for compound indexes.

Another scenario involves queries where one property narrows down the dataset significantly, and a second property provides additional filtering within that subset. The compound index can efficiently handle both steps in a single lookup operation. However, compound indexes are less helpful when your queries filter on different property combinations each time, or when you often search by just one of the indexed properties in isolation.

Let's create a compound index that will help us efficiently find users by city and age together. The syntax extends what we learned for single-property indexes:

// Index on city and age together
CREATE INDEX user_city_age FOR (user:User) ON (user.city, user.age)

The key difference from single-property indexes is the property list in the ON clause. Here, we specify (user.city, user.age), creating a multi-dimensional index structure. The name user_city_age follows a helpful convention: including both properties reminds us what the index covers. Once created, this index will organize all User nodes first by their city value, then by age within each city. This two-level organization enables efficient lookups when queries constrain both properties.

The order in which you list properties in a compound index is critical and affects whether Neo4j can use the index at all. Compound indexes follow a leftmost prefix rule: the query planner can only use the index when your query filters on the first property, the first and second properties, or all properties in order. The index cannot help if you skip the first property and only filter on later ones.

Consider our (city, age) index:

• WHERE user.city = 'New York' can use the index (first property).
• WHERE user.city = 'New York' AND user.age > 30 can use the index fully (both properties).
• WHERE user.age > 30 typically cannot use this index (skips the first property).

This behavior stems from how the index structure organizes data. Since nodes are sorted first by city, searching for age alone would require scanning through all cities, defeating the purpose of the index. When designing compound indexes, always put the property you filter on most consistently in the first position.

Beyond the leftmost prefix rule, the type of predicates also affects how effectively Neo4j uses compound indexes. Equality conditions work best because they pin down exact values in the index structure. Range conditions, such as greater-than or less-than comparisons, are also supported, but they change how subsequent properties can be used.

When the query uses a range condition on an earlier property, later properties often become post-filters rather than index-seek criteria. For example, with our (city, age) index:

• WHERE user.city = 'New York' AND user.age > 30 works efficiently (equality then range).
• WHERE user.city STARTS WITH 'New' AND user.age = 30 may only leverage city for seeking.

This distinction matters for performance tuning. Queries with equality conditions on all indexed properties achieve the most selective seeks, while mixing equality and range operations requires understanding how the planner will interpret them.

Now let's use our compound index in an actual query. This query finds users in New York who are over 30 years old:

// Query that uses compound index
MATCH (user:User)
WHERE user.city = 'New York' AND user.age > 30
RETURN user.name

Output:

user.name
"Eve"
"Bob"
"Charlie"

This query satisfies the leftmost prefix rule by filtering on city first (as an equality check), then age (as a range). The query planner will use our user_city_age index to:

1. Seek directly to nodes with city = 'New York'.
2. Within that city group, navigate to nodes where age > 30.
3. Return only the matching nodes.

Without the compound index, Neo4j might use a single-property index on city (if available) and then filter by age, or worse, scan all User nodes. The compound index eliminates unnecessary node examinations by leveraging both properties in the index structure.

You might wonder: why not just create separate indexes on city and age instead? While Neo4j can sometimes use multiple single-property indexes together through a technique called index intersection, compound indexes typically perform better for queries that consistently filter on the same property combination.

Single-property indexes require the query planner to:

• Seek using one index to find candidate nodes.
• Either seek using another index and intersect the results or filter the candidates.

This intersection operation adds overhead. A compound index provides a direct path to the final result set in one operation. However, single-property indexes offer more flexibility: they help queries that filter on either property alone. The choice depends on your query patterns:

• Use compound indexes when queries consistently filter on the same multiple properties.
• Use separate single-property indexes when queries filter on different individual properties.

Compound indexes come with maintenance costs that affect write performance. Every time you create, update, or delete a node with indexed properties, Neo4j must update the index. For compound indexes, this means updating the index whenever any of the indexed properties changes.

If you modify a user's city, their age, or both, the compound index entry must be rebuilt. This creates write amplification similar to single-property indexes, but with wider key values that combine multiple properties. The trade-off becomes clear:

• One compound index requires updating one structure, but with more complex keys.
• Two single-property indexes require updating two separate structures with simpler keys.

Neither approach is strictly cheaper; the best choice depends on your specific workload. Write-heavy applications should carefully consider whether the query performance gains justify the index maintenance overhead. Always monitor both read and write performance after adding indexes to ensure they provide net benefits for your use case.

In this lesson, we've explored compound indexes and how they optimize queries that filter on multiple properties simultaneously. We learned that compound indexes organize data hierarchically based on property order and that this order determines whether the query planner can use the index through the leftmost prefix rule.

We created a compound index on city and age, demonstrated how queries can leverage it effectively, and discussed the trade-offs between compound indexes and multiple single-property indexes. We also examined the write amplification that comes with maintaining these more complex index structures. Understanding when to use compound indexes versus single-property alternatives is an essential skill for optimizing graph database performance. Ready to practice what you've learned? Let's create some compound indexes and see them in action!