What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Reversing the sort direction of a composite index in Cloudflare D1 changes the query plan only when the reversed key order lets SQLite satisfy the query’s ORDER BY (or a filter) without a separate sort. Whether that happens depends on your SQL, your schema and the planner’s cost estimate, so the only reliable answer comes from comparing EXPLAIN QUERY PLAN output before and after the change.
Why index direction can change the plan at all
D1 is built on SQLite, so its query planner follows SQLite’s rules. SQLite can read an index backwards, which means an index declared ASC can serve a DESC ordering and the reverse. What the index cannot do is produce an arbitrary mix of directions. A multi-column index is stored ordered by its leftmost key first, with each following key breaking ties. The planner can use the index for ordering only when the requested ORDER BY matches that sequence, or its exact reverse, after any leading columns fixed by equality constraints are removed.
As an Amazon Associate I earn from qualifying purchases.
That is why direction matters. Reversing an index is not a generic speedup. It is a change in which orderings the index can deliver directly. If the query already gets its order from the existing definition, the reversal can leave the plan unchanged. If the query’s ordering is the exact opposite of what the index provides, the reversal may remove a sort. If the query mixes directions across several sort keys, a reversal may still leave a sort in place.
Recommended Free Tools
Working through a two-column example
Take an index on (account_id ASC, created_at DESC) and a query that filters on one account:
#1 Best Overall
- Query A:
WHERE account_id = ? ORDER BY created_at DESC. The index already stores each account’s rows newest first, so the planner can walk the matching range in order. - Query B:
WHERE account_id = ? ORDER BY created_at ASC. The planner can walk the same range backwards. The equality onaccount_idfixes the leading key, so the reverse traversal gives oldest first within that account. - Query C:
WHERE account_id = ? ORDER BY created_at ASC, id DESCwith an index that has noidkey. The index cannot supply the second term, so a temporary sort remains.
Reversing a full index definition flips every key’s direction at once. A reverse traversal therefore gives you the opposite of the whole stored sequence, never one column flipped alone. If your query needs created_at ASC, status ASC while the index stores created_at DESC, status ASC, no single reversal satisfies it, and you need a different index definition or an accepted sort.
Reading the plan output
SQLite’s EXPLAIN QUERY PLAN output, documented at https://a1.sqlite.org/eqp.html, is the evidence to compare. Three details matter most:
Rank #2
- The index name. The plan should name the index you intended. If it names another index or a full table scan, the planner chose a different strategy.
- A temporary B-tree line. A line such as
USE TEMP B-TREE FOR ORDER BYmeans SQLite is sorting results itself. Its absence suggests the index supplies the order, though you should confirm against the query shape. - The verb.
SEARCHusually indicates a lookup constrained by an index key.SCANcan also describe iterating through an index in order, so read the full detail line instead of treating everySCANas a whole-table read.
Cloudflare’s guidance on indexes, updated August 10, 2026, states that a multi-column index is used when a query references its leftmost column or a leftmost prefix, and recommends EXPLAIN QUERY PLAN to confirm index use: https://developers.cloudflare.com/d1/best-practices/use-indexes/.
Outdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchPC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11How to test a direction change in D1
An existing index cannot be altered in place, so the test is a drop and recreate. Keep the table and query fixed, and work through these steps:
- Record the current definition. Query
SELECT sql FROM sqlite_schema WHERE type = 'index' AND tbl_name = 'events';, replacingeventswith your table. For per-column detail, runPRAGMA index_xinfo('idx_account_created');with your index name, which shows each key’s sort direction. - Capture the baseline plan. Run
EXPLAIN QUERY PLANon the exactSELECT, including itsWHERE,ORDER BYandLIMITclauses, and save the output. - Swap the index. Run
DROP INDEX idx_account_created;, thenCREATE INDEX idx_account_created ON events (account_id ASC, created_at ASC);or whichever direction you are testing. - Refresh statistics. Run
PRAGMA optimize;after creating the index, as Cloudflare recommends, so the planner’s statistics reflect the new definition. - Compare plans. Rerun the same
EXPLAIN QUERY PLANand compare the index name, the temporary B-tree line and the scan or search wording.
Plan changes are not runtime results
A changed plan shows a different strategy, not a faster query. Runtime depends on data volume, cache state, selectivity and row width, so measure both before and after on representative data. D1 bills by rows read and written, so the row counts a query reads are also worth recording alongside latency. The official sources do not publish a benchmark for how much index direction alone changes runtime, so treat any percentage you encounter elsewhere with caution.
The planner also works from cost estimates. An index that fits the query can still be ignored if the planner estimates another route to be cheaper. SQLite describes this cost-based approach in its query planning documentation at https://www.sqlite.org/queryplanner.html. If the plan does not change after a reversal, check whether the planner already had a comparable strategy, and whether PRAGMA optimize refreshed the statistics it uses.
Checklist before you keep a reversed index
- The index’s key order and directions match the query’s
ORDER BYsequence, or its exact reverse, after equality-constrained leading keys are removed. - The
WHEREclause constrains the leftmost key or a leftmost prefix. - The plan names the intended index and no temporary B-tree appears for ordering.
- Rows read and latency were measured on representative data, both before and after the change.
- Other queries that use the same table were checked, because a direction that helps one query may leave another sorting.
For the detailed syntax of statements in D1, see Cloudflare’s SQL statements reference, last updated April 21, 2026: https://developers.cloudflare.com/d1/sql-api/sql-statements/.
Quick Recap
“
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
