Why My MySQL Index Is Ignored When Filtering and Ordering

In the world of database optimization, indexes are the primary mechanism for transforming a sluggish application into a high-performance system. For most developers, the logic seems straightforward: if you have a WHERE clause filtering by a column and an ORDER BY clause sorting by another, creating a composite index on both should guarantee a fast response. However, it is a common and frustrating experience to run an EXPLAIN plan only to discover that MySQL is completely ignoring your index in favor of a costly full table scan or a temporary filesort.

This discrepancy occurs because the MySQL index optimizer does not simply look for the presence of columns in an index; it follows a strict set of internal rules regarding the left-most prefix and the sequence of data access. When a query mixes filtering and sorting, the optimizer must decide whether it is more efficient to use an index to narrow down the result set or to use an index to avoid a manual sort operation. If the columns are not ordered precisely as the optimizer expects, it will deem the index useless for one of the two operations.

Understanding this decision process is critical. When the optimizer chooses a sub-optimal path, CPU usage spikes and query latency increases linearly with your data growth. By mastering how MySQL evaluates composite indexes, you can move beyond guesswork and design data structures that ensure predictable, scalable performance.

How MySQL Chooses an Index for Mixed Filtering and Sorting

MySQL’s query optimizer evaluates every possible access path to satisfy a SELECT, considering both the WHERE clause and the ORDER BY requirement. The first rule it follows is the left‑most column rule: a composite index can be used for filtering only if the predicate references the leftmost columns of the index in the same order. For example, an index (customer_id, order_date, status) can quickly locate rows where customer_id = 5 and then further restrict order_date > '2023-01-01'. If the WHERE clause references order_date without also referencing customer_id, the optimizer cannot use the index for filtering.

When the optimizer can use an index for filtering, it then checks whether the same index can also provide the required ordering. If the ORDER BY columns appear after the filtered prefix and are not mixed with non‑indexed expressions, MySQL can avoid a filesort. A covering index (one that contains all columns needed by the query) makes this even more efficient because the engine can satisfy the query entirely from the index.

MySQL also supports index merge – combining two or more single‑column indexes to satisfy a WHERE clause when no single composite index exists. Index merge works for pure filtering but does not help ORDER BY unless one of the merged indexes also matches the sort order, which is rare.

Examples illustrate the behavior:

Successful query

EXPLAIN
SELECT * FROM orders
WHERE customer_id = 5 AND order_date > '2023-01-01'
ORDER BY order_date;

With an index (customer_id, order_date), MySQL uses range on customer_id and then scans order_date in order, avoiding a sort.

Failed query

EXPLAIN
SELECT * FROM orders
WHERE order_date > '2023-01-01' AND status = 'shipped'
ORDER BY order_date;

Even though (order_date, status) matches the filter, the optimizer cannot use the index for ordering because the range on order_date breaks the ordered prefix, leading to a full table scan and an extra filesort.

Understanding these interactions lets developers design indexes that let MySQL stay on the optimal access path, reducing I/O and improving response times.

Practical Ways to Make the Index Work\n\nWhen MySQL ignores a composite index while you filter on one column and sort on another, a few practical adjustments can force the optimizer to pick a useful path.\n\n### Redesign the Composite Index\nThe most common fix is to reorder columns so the left‑most prefix matches both WHERE and ORDER BY clauses. Suppose you have:\n\n

sql\nCREATE INDEX idx_orders_status_date ON orders (status, created_at);\n

\n\nand you run:\n\n

sql\nSELECT * FROM orders WHERE status='open' ORDER BY created_at ASC;\n

\n\nEXPLAIN shows “type: ALL, possible_keys: ” because status is first but the order condition is not covered. By swapping the columns:\n\n

sql\nCREATE INDEX idx_orders_date_status ON orders (created_at, status);\n

\n\nand running the same query, MySQL uses the index for filtering (if you add status='open' to the WHERE) and avoids a filesort. The trade‑off is that the index is smaller for filtering on created_at but larger for queries that filter only on status.\n\n### Add a Second Index\nIf a single index cannot satisfy both clauses without a large penalty, consider adding a narrow index for the ORDER BY side, e.g.:\n\n

sql\nCREATE INDEX idx_orders_created_at ON orders (created_at);\n

\n\nNow the optimizer can use idx_orders_status_date for the WHERE and idx_orders_created_at for the ORDER BY, possibly performing an index merge. The downside is extra storage and write overhead.\n\n### Use FORCE INDEX\nWhen you need deterministic performance for a critical query, force a specific index:\n\n

sql\nSELECT * FROM orders USE INDEX (idx_orders_date_status) WHERE status='open' ORDER BY created_at;\n

\n\nThe FORCE INDEX (or USE INDEX) hint tells MySQL to ignore its cost‑based choice. This guarantees the index is used, but if data distribution shifts, the query may become suboptimal. Use it sparingly and monitor execution times.\n\n### Rewrite the ORDER BY\nSometimes reordering the SELECT columns or adding a derived condition lets the optimizer treat the ORDER BY as a covering operation. For example, adding a column that appears in the index to the SELECT clause can turn a regular read into a covering read:\n\n

sql\nSELECT created_at, status FROM orders WHERE status='open' ORDER BY created_at;\n

\n\nIf created_at,status is an index, the query can be satisfied entirely from the index (COVERING in EXPLAIN). This reduces I/O at the cost of returning extra columns.\n\n### Optimize with a Covering Index\nA covering index includes every column referenced in the query. If the original query selects many columns, consider a composite covering index that also includes a few extra fields:\n\n

sql\nCREATE INDEX idx_orders_cover ON orders (status, created_at, id, amount);\n

\n\nNow the query SELECT * FROM orders WHERE status='open' ORDER BY created_at can be answered solely from the index because id and amount are covered. The trade‑off is larger index size and slower inserts/updates.\n\n### When to Apply Each Technique\n- Reorder columns – best first step, minimal impact.\n- Add a second index – useful when filtering and sorting are mutually exclusive.\n- FORCE INDEX – for predictable latency on hot paths.\n- Rewrite ORDER BY – when you can adjust the SELECT list.\n- Covering index – for read‑heavy tables with wide result sets.\n\nBy experimenting with these approaches and reviewing EXPLAIN output after each change, you can steer MySQL toward the most efficient access path. For systematic index audits and automated optimization, Paradane\u2019s team can help you integrate the right indexing strategy into your development workflow.\n

Putting It Into Practice on Your Project

Checklist for auditing and fixing index usage

1. Identify queries – Use EXPLAIN (or EXPLAIN ANALYZE) on all SELECT statements that filter or order. Capture the execution plan.
2. Check index usage – Look at the key column; if NULL, the optimizer is not using an index.
3. Validate index structure – Verify that a composite index exists covering the WHERE columns in the correct left‑most order and that the ORDER BY columns match a left‑most prefix.
4. Examine type and rows – Prefer ref or ref/range over ALL (full table scan).
5. Check rows estimate vs actual – High row estimates may indicate missing statistics; run ANALYZE on the tables.
6. Inspect key and key_len – Ensure the correct index is selected; key_len should match the column widths.
7. Check Using filesort – If present, sorting is happening outside the index; consider re‑ordering the index or rewriting the query.
8. Apply fixes:
   • Redefine the composite index to include the ORDER BY columns in the correct left‑most order.
   • Add a secondary index that matches the ORDER BY columns if they are not part of the existing index.
   • Use FORCE INDEX only as a temporary measure; prefer index redesign.
   • Add a covering column (e.g., include the SELECT fields) to make the index covering and avoid a filesort.
9. Re‑run EXPLAIN after each change and verify that key is set and Using filesort disappears.
10. Monitor performance – Use MySQL’s Performance Schema (events_statements_summary_by_digest), the Performance Schema UI, or tools like pt‑query‑digest, Percona Monitoring and Management, or MySQL Enterprise Monitor to track query latency and index usage over time.
11. Automate checks – Integrate EXPLAIN output into CI pipelines or use tools like Percona Toolkit’s pt-index-usage to detect unused indexes.

Monitoring tools:

• MySQL Performance Schema (events_statements_summary_by_digest)
• Percona Monitoring and Management (PMM)
• MySQL Enterprise Monitor
• pt‑query‑digest (Percona Toolkit)
• MySQL Workbench Performance Reports

Next steps

• Run the checklist on your staging environment.
• Record before/after EXPLAIN output to verify improvements.
• Deploy changes in a controlled rollout and monitor latency.

Explore Paradana’s expertise to design robust, index‑efficient schemas for your real‑world applications.

---

Example EXPLAIN before and after

---

Before (index ignored):

EXPLAIN SELECT * FROM orders WHERE status = 'shipped' ORDER BY created_at DESC;

| id | select_type | table   | type   | possible_keys | key   | key_len | rows | Extra          |
|----|-----------|---------|--------|---------------|-------|---------|------|----------------|
|  1 | SIMPLE    | orders  | ALL    | idx_status    | NULL  | NULL    | 12000| Using filesort |

After adding a covering index

CREATE INDEX idx_status_created ON orders(status, created_at);

| id | select_type | table   | type   | possible_keys | key               | key_len | rows | Extra          |
|----|-----------|---------|--------|---------------|-------------------|---------|------|----------------|
|  1 | SIMPLE    | orders  | ref    | idx_status    | idx_status        | 400     |   12 | Using where   |

Monitoring tip: run SELECT * FROM performance_schema.events_statements_summary_by_digest WHERE digest_text LIKE '%orders%'; to see if the query now uses the index.