Optimizing Queries in Magic IndexedDB
Author: Lance Wright II
Published: March 26, 2025
Optimizing Queries in Magic IndexedDB
1. Understanding Query Partitioning in IndexedDB
What is Query Partitioning?
Partitioning is the process of analyzing your LINQ query and breaking it down into optimized IndexedDB queries.
Magic IndexedDB examines your predicates and determines if they can:
✅ Use an Indexed Query (Best performance)
✅ Leverage a Compound Index Query (Optimized for multi-column lookups)
✅ Fallback to a Cursor Query (Last resort when indexes can’t be used)
How Partitioning Works:
When you write a LINQ expression, Magic IndexedDB scans for OR (||) operations and breaks them into separate queries.
Example Query:
var results = await _MagicDb.Query<Person>()
.Where(x => x.Email == "user@example.com" && x.Age > 30 || x.IsReallyCool)
.ToListAsync();
This gets partitioned into:
1. x.Email == "user@example.com" && x.Age > 30
2. x.IsReallyCool == true
Each AND (&&) condition inside an OR group is analyzed to determine:
✅ Can it be an Indexed Query?
✅ Can it be a Compound Index Query?
✅ If not, it falls back to a Cursor Query.
🚨 If each condition connected by the (&&) operations aren't able to be a single indexed query, then the entire grouped operations are sent to the cursor. Every connected piece must be an indexable query or none if it can for that (||) operation.
2. The Three Types of Queries in IndexedDB
✅ Indexed Queries (Best Performance)
An indexed query happens when all conditions in an AND (&&) group match a single field index.
await _MagicDb.Query<Person>().Where(x => x.Email == "user@example.com").ToListAsync();
✅ Uses an Indexed Query → Fast lookup using IndexedDB’s native .where() method.
Tip: Simple equality (==) operations on indexed fields perform best.
🔗 Compound Index Queries (Optimized Multi-Column Lookups)
A compound index lets you query multiple fields efficiently—note if you're familiar with IndexedDB, the order normally matters. Magic IndexedDB doesn't care, it'll order and optimize for you..
await _MagicDb.Query<Person>().Where(x => x.LastName == "Smith" && x.FirstName == "John").ToListAsync();
✅ Uses a Compound Index Query → If LastName and FirstName are indexed together.
🚨 Cursor Query Engine (Advanced Last-Resort Execution)
In Magic IndexedDB, a cursor query is the system’s final query layer — but don’t confuse it with a typical, slow IndexedDB cursor scan. This is a custom multi-phase engine built for performance, precision, and memory control when no index or compound path can satisfy the full predicate.
await _MagicDb.Query<Person>().Where(x => x.Name.Contains("John")).ToListAsync();
✅ Not your standard cursor: A single cursor instance intelligently handles all remaining || operations not covered by indexed or compound queries.
🚀 Predicate optimized: Automatically restructures the conditions for early bailouts and reduced iteration costs.
🧠 Index-aware cursor: Skips any rows already returned by prior indexed queries — no redundant processing.
⚙️ Multi-Stage Cursor Query Execution
Stage 1 – Scan Phase: The cursor iterates only on rows not already returned, checking conditions in a reordered, optimized predicate tree.
Stage 2 – Meta-Only Evaluation: If Take(), Skip(), First(), or TakeLast() are present, only minimal meta-data is captured (e.g. primary key, sorting fields).
Stage 3 – In-Memory Logic Phase: A simulation of IndexedDB ordering + LINQ semantics applies the remaining pagination and sort logic in memory, using only that meta-data.
Stage 4 – Final Fetch: Only after knowing exactly which records are required does the system fire bulk primary key queries to load the actual data.
This means even for fallback paths, Magic IndexedDB avoids unnecessary memory allocation and keeps data movement minimal. Cursor queries become as close as possible to SQL-like row-by-row execution when filtering against non-indexed fields.
💡 Optimization Tip
Cursor queries are still a last resort — indexed and compound-indexed queries remain faster — but thanks to this engine, you don’t need to fear performance collapse if you hit a .Contains() or case-insensitive match. Just understand the stages so you can write queries that allow it to shine.
This system opens the door to future enhancements like hot caching, predictive lookahead, and hybrid IndexedDB/in-memory optimizations. It’s not just a workaround — it’s a foundation.
3. The Optimization Process
Step 1: Partitioning the Query
1. Break apart the query by OR (||) conditions.
2. Process each AND (&&) group separately.
Step 2: Checking for Compound Index Queries
1. Check if all AND conditions match a known compound index.
2. If yes, execute a Compound Query.
Step 3: Checking for Indexed Queries
1. Check if all AND conditions can be optimized as indexed queries.
2. If yes, execute an Indexed Query.
Step 4: Indexed Query Optimizations
1. All indexed queries goes through an optimization process. This will combine items into AnyOf() operations or remove redundant paths, and more.
Step 5: Fallback to Cursor Query
1. If the query cannot be optimized, execute a Cursor Query.
2. The optimized cursor query process begins the scan for final retrieval.
Please note this is a basic summarized version of the process. Your queries are not only optimized, but utilizing read transaction, async parallel processing, and performs multiple levels of dark magic to never load anything into memory until we 100% know it's what we want.
4. How Query Additions Affect Optimization
Understanding Query Additions
Query additions modify your query structure by introducing sorting, pagination, or limits. While some additions fully utilize IndexedDB’s indexing, others require transformations or force a cursor fallback.
Magic IndexedDB intelligently optimizes query additions to:
✅ Keep queries indexed whenever possible
✅ Leverage compound indexes and order optimizations
✅ Only force a cursor when necessary
Most important note on this topic is that the system utilizes multiple parallel running queries. But, if you have a long operation with multiple (||) operations and you ask for the first or default item, or you want to take 5, or skip 3. Well, if we run this process in parallel, you'd likely get multiple results from each query, which was not your original intent. Therefore, the partitioning layer understands that if you request such an action, it must all be one query. Therefore if the entire predicate can't be made into a single indexed query then it must all run as a single cursor query.
🚨 When Does a Query Become a Cursor Query?
1. At least one AND (&&) group cannot be expressed as a single indexed or compound index query.
2. A non-indexed field is used in sorting (OrderBy) or filtering.
3. A query addition (like Skip) is used on an unindexed query.
4. A query addition (like Take) is used and all operations can't be fired as a single indexable query.
🚀 Query Addition Rules & IndexedDB Optimizations
| Addition | Effect |
|---|---|
.OrderBy(x => x.Age) |
✅ Optimized if Age is indexed |
.OrderBy(x => x.NonIndexedField) |
❌ Forces a cursor query (Ordering requires an index) |
.Skip(10).Take(5) |
✅ Optimized when query is indexed |
.Take(10) |
✅ Optimized if query is indexed |
.TakeLast(10) |
✅ Optimized via smart transformation |
.FirstOrDefaultAsync() |
✅ Indexed if ordering is indexed |
.LastOrDefaultAsync() |
✅ Optimized via reverse query transformation |
Please note this logic also applies to OrderByDescending. But also note that the dark arts were performed to make multiple standard OrderByDescending operations work as an indexable operation on your behalf. They system knows when to forsake TakeLast for a Take with an OrderBy().Reverse() and so on and so forth.
💡 How TakeLast() is Indexed
Normally, IndexedDB does not natively support TakeLast(), but Magic IndexedDB transforms the query to achieve the same effect using indexed operations:
1. Reverses sorting order (OrderByDescending)
2. Applies Take(n) to retrieve the last n elements efficiently
3. Returns results in the correct order after retrieval
✅ Optimized Example (Indexed Query)
await _MagicDb.Query<Person>()
.OrderBy(x => x.Age)
.TakeLast(5)
.ToListAsync();
🔹 Translates into:
table.where("Age").above(0).reverse().limit(5)
🚀 Efficient and fully indexed!
⚠️ When a Query Becomes a Cursor
Queries only fall back to a cursor if they cannot be fully executed using indexed queries.
🚨 Cursor Example (Due to Non-Indexable Condition)
await _MagicDb.Query<Person>()
.Where(x => x.Email == "user@example.com" || x.Age > 30 || x.Name.Contains("John"))
.Take(5)
.ToListAsync();
🚨 Forces a cursor query because:
Email == "user@example.com" → ✅ Indexed
Age > 30 → ✅ Indexed
Name.Contains("John") → ❌ Not indexed (Requires full scan)
➡ Because one condition is unindexable, the entire query must be executed as a cursor.
✅ Summary: Optimizing Queries with Additions
✅ Use indexed fields whenever possible
✅ Leverage .TakeLast() only when an indexed field is used for ordering
✅ Always use OrderBy() on an indexed field
🚨 Understand which operations like Contains() are going to cause a cursor or indexed query to fire. As each situation can depend.
💡 Remember:
Magic IndexedDB pushes IndexedDB to the limit by transforming queries intelligently, ensuring maximum performance while preserving accurate intent. 🚀
5. Best Practices for Writing Optimized Queries
✅ Key Takeaways to Maximize Performance
✅ Use indexed fields as much as possible.
✅ Leverage compound indexes for multi-field lookups.
✅ Minimize OR (||) operations—they create multiple queries.
🚫 Understand when and what operations causes a cursor fallback
6. Deep Dive: The Magic IndexedDB Optimization Layer
How Does Optimization Work?
Magic IndexedDB includes an advanced optimization layer that:
✅ Compresses queries into fewer IndexedDB requests.
✅ Rearranges conditions to maximize index usage.
✅ Combines multiple queries into a single efficient query when possible.
🔍 How Query Compression Works
For each || operation, the query will always result in the same or fewer queries.
✅ Optimized Example:
await _MagicDb.Query<Person>()
.Where(x => x.Age > 30 || x.Age < 20 || x.Age == 25)
.ToListAsync();
🔹 This will be optimized into a single query:
table.where("Age").anyOf([25]).or(table.where("Age").above(30)).or(table.where("Age").below(20))
This combines multiple queries into a single efficient IndexedDB query. Please note this example may not fully represent the actualy query built on your behalf. The optimization phase builds things on your behalf in a variety of directions. Additionally, the optimizations may be upgraded or changed in future updates to provide more intelligent operations over time.
⚡ Examples Of Query Compression Techniques Used
| Optimization | How It Works | Example Transformation |
|---|---|---|
| Merges Equality Conditions | x.Age == 30 || x.Age == 25 |
anyOf([25, 30]) |
| Converts Ranges to BETWEEN | x.Age > 30 && x.Age < 40 |
between(30, 40) |
| Combines Queries for Efficiency | x.Name == "John" || x.Name == "Jane" |
anyOf(["John", "Jane"]) |
| Avoids Redundant Queries | Removes unnecessary conditions | x.Age > 30 && true → x.Age > 30 |
🚀 These optimizations make queries up to 10x faster!
🎯 How IndexedDB Limitations Affect Optimization
IndexedDB is powerful but has some limitations that impact query optimization.
🚀 Things IndexedDB is Good At
✅ Fast Indexed Lookups (e.g., .where("ID").equals(5))
✅ Efficient Range Queries (e.g., .where("Age").between(20, 30))
✅ Compound Indexes (e.g., .where(["LastName", "FirstName"]).equals(["Smith", "John"]))
✅ Sorting & Pagination (when indexed)
⚠️ IndexedDB Limitations
❌ No Native LIKE or Contains() Queries
❌ No ORDER BY on non-indexed fields
❌ No TakeLast() or Reverse Pagination – Well normally... We got you covered though!
❌ No Joins or Complex Aggregates
Magic IndexedDB works around these limitations by:
✅ Using cursors where needed.
✅ Rewriting queries for optimal execution.
✅ Applying query compression techniques.
The Cursor Query: How Magic IndexedDB Handles Non-Indexed Queries
🧐 What is a Cursor?
A cursor in IndexedDB is similar to how SQL processes row-by-row searches when no index is available. It scans the entire dataset, checking each record to see if it meets your query conditions.
Since IndexedDB does not support complex filtering (like case-insensitive searches or Contains() on non-indexed fields), a cursor must be used to process those queries.
In Magic IndexedDB, the cursor only runs when absolutely necessary, and when it does, it does so in the most efficient way possible. By leveraging meta-data partitioning and batching optimizations, Magic IndexedDB makes cursor queries as performant as possible while maintaining low memory overhead.
🚀 How the Cursor Works in Magic IndexedDB
🔍 Step 1: Partitioning the Query
First, your query is broken into multiple AND (&&) and OR (||) groups.
Any AND group that cannot be expressed as an indexed or compound indexed query must go into the cursor.
If any part of an OR group contains a non-indexable condition, the entire OR group must be processed by the cursor.
🧠 Step 2: Collecting Only Meta-Data
Instead of loading full database records into memory, Magic IndexedDB only collects meta-data during the cursor scan:
✅ Primary Keys (for fetching actual data later)
✅ Indexed Fields (to preserve ordering & filtering intent)
✅ Fields involved in sorting or pagination
🚨 No full data is loaded yet! This minimizes memory usage and keeps things efficient.
📑 Step 3: Processing the Meta-Data
Filters out unnecessary records immediately
Applies sorting (OrderBy, OrderByDescending)
Handles pagination (Take, TakeLast, Skip)
Only retains the necessary primary keys
At this stage, Magic IndexedDB knows exactly what records need to be fetched.
📦 Step 4: Bulk Fetching in Batches
Once the required primary keys have been determined, Magic IndexedDB sends out bulk queries in batches of 500 records per request.
✅ Avoids overwhelming IndexedDB with massive single queries
✅ Optimizes anyOf() performance when dealing with OR (||) conditions
✅ Efficiently pulls the remaining required data for final processing
⏳ Step 5: Yielding Data Efficiently
✅ No need to wait for the full query to finish
✅ Each batch is processed and returned in real-time
✅ Keeps memory footprint low by never loading unnecessary data
🛠️ Why the Cursor is Powerful in Magic IndexedDB
✔️ Supports case-insensitive searches (e.g., StringComparison.OrdinalIgnoreCase)
✔️ Handles unsupported IndexedDB operations (e.g., Contains())
✔️ Ensures that even cursor-based queries follow LINQ-style ordering & pagination rules
✔️ Optimized for memory efficiency using meta-data filtering
✔️ Smart batching prevents IndexedDB from slowing down under heavy OR queries
💡 Cursor Performance: What You Need to Know
While Magic IndexedDB optimizes cursor queries, they are still slower than indexed queries. Your goal should always be to write queries that take full advantage of indexing whenever possible.
🔹 Best Practices for Faster Queries
✅ Use indexed fields whenever possible
✅ Leverage compound indexes for multi-condition queries
✅ Avoid Contains() on large datasets unless necessary
✅ Minimize OR (||) operations, as each OR condition can trigger separate queries
🚀 Remember: Magic IndexedDB gives you maximum flexibility, but indexed queries are always faster than cursor queries. The more you optimize your query structure, the faster your queries will run.
Cursor Undefined Columns Danger
A major philosophy of this project is to never fail client side. Because failure would be catastrophic for disconnected client-side storage. So, let’s go over an example of a real-world scenario that the cursor can handle—but how it handles it is critical.
Example
You create a table with the columns of id, name, and age. This is now deployed for all users. But then, in a future update, you add a new column called dateOfBirth that isn’t indexed.
If you then write a LINQ query referencing dateOfBirth, the cursor will try to access this column—even though it's undefined in some users' existing rows. Since part of the database only had 3 columns historically, and new data has 4, this creates a mismatch in structure.
Cursor Fallback
In this scenario, the cursor will recognize it’s about to access an undefined column. It will automatically skip that entire row from the query.
If it can't evaluate all predicate operations due to missing fields, none will execute on that row. This design prevents client-side failures and avoids retrieving incomplete or broken data.
The Fix?
There’s not necessarily a “fix,” but there are best practices.
✅ As the Magic IndexedDB migration system rolls out, it will help automatically update old records with default values—making this problem disappear entirely.
⚠️ If you’re not using the migration system yet, make sure all new columns are defined with proper defaults when updating your schema.
💡 Magic IndexedDB is Evolving—Help Make it Even Better!
Magic IndexedDB pushes IndexedDB to its absolute limits, but there’s always room for improvement!
🚀 Want to see even more optimizations?
🧠 Have an idea for new features?
🤝 Join the project and help make IndexedDB the powerful database it should be!
Final Thoughts – Ordering Logic
You’ve nearly learned all the fundamentals of Magic IndexedDB! But there's just one more thing, I promise (fingers crossed)!
Did you know that the ordering isn't applied to the returned results? Sounds crazy, right? But it makes a ton of sense—and it’s important to understand.
