A Composite Index Is Built, but the Query Uses Only the Last Column and Still Does a Full Table Scan—Should You Add a Single-Column Index?

Conclusion first: If a composite index exists but a query uses only the last column and still does a full table scan, the common reason is that the query does not hit the leftmost prefix of the composite index, not necessarily that the column order was written backwards. Based on 2026 project delivery habits, first look at key, rows, filtered, and Extra in the execution plan, then judge in combination with high-frequency queries, range conditions, sorting, and the cost of table lookups. If it can be solved by rewriting SQL or adding a covering index, do not rush to rebuild the composite index; when the table is small or filtering still returns a large number of rows, a full table scan may be more economical.

Why a Composite Index May Not Be Used When the Query Includes Only the Last Column

A composite index (a,b,c) is sorted by a first in the B+ tree, then by b when a is the same, and then by c when b is the same. If a query only writes where b=? and c=?, it lacks the leftmost column a. The database usually cannot start positioning from the left side, so it can only do a full table scan or an inefficient index scan. With different column orders, the execution plans for the same set of SQL may differ—this is where composite indexes easily lead to misjudgment.

Another common case is when a range condition appears early. In where a>? and b=?, the range scan on a has already cut off subsequent columns, so b can often only serve as a filter condition and cannot continue to narrow the index range. At this point, the index is not invalid; it only covers the first part.

What Column Order Is More Stable?

When ordering columns, do not only ask which field is important. Look at whether query conditions can form a continuous prefix, whether range conditions will truncate subsequent columns, whether sort columns can connect, and whether write costs are acceptable. A common practice in 2026 is to put equality conditions first, range conditions later, and sort columns immediately after equality columns, then fine-tune by query frequency and update frequency.

• Equality columns first: conditions such as where a=? and b=? should go at the front, making it easier to form an effective prefix.
• Range columns later: columns after a>? or between usually cannot continue to be used for positioning.
• Sort columns connected: the columns and directions in order by should match the index as much as possible to reduce filesort.
• Selectivity reference: high-selectivity columns are not necessarily placed first; satisfy the leftmost prefix and query frequency first.
• Write cost: place frequently updated columns less often; the larger the index, the higher the pressure on writes and replication delay.

The experience range is that after a composite index reaches 3 to 5 columns, its benefit should be re-evaluated; the more columns, the heavier the write maintenance. This range is not a fixed standard; it is just a reminder not to assume that more columns are always better.

Change the Order or Add a Single-Column Index: A Verifiable Comparison

Adjusting column order is not the only solution. The following comparison is based on common delivery scenarios; cost and duration are experience ranges and actually depend on table size, database type, write pressure, and release process.

• Option A: Adjust the composite index column order. Suitable when high-frequency query patterns are stable and the combination of equality, range, and sorting is clear. The cost is one online DDL, with an experience range from a few minutes to a few hours; the risk is write amplification and primary-replica delay, so rollback should be prepared.
• Option B: Keep the original index and add a single-column index or covering index. Suitable when there are many query patterns and column order is hard to unify. The cost is adding indexes or changing code, with an experience range from half a day to several days; the risk is an increased number of indexes, more optimizer choices, and more complex maintenance.
• Option C: Rewrite SQL to reduce functions wrapping columns, implicit conversions, or adjust where/order by. Suitable when SQL is controllable and the release pipeline is smooth. The cost is development and testing, with an experience range from a few hours to several days; the risk is easily missing other callers.
• Option D: Do nothing for small tables or low-selectivity queries. When the data volume is small and full table scans are stable within acceptable time, the cost is close to zero, but you must accept that scans will slow down as data grows.

When choosing, first ask three questions: Will changing the index benefit most high-frequency queries at the same time? Can it be solved without changing the structure by rewriting SQL or adding a covering index? Can the write side bear the new index? Adjustments that only affect a few low-frequency queries are usually not worth touching the online index.

Constraints and Costs on the Delivery Site

In recent enterprise project deliveries, common constraints are limited budget and downtime windows, tables already with tens of millions of rows of data, and SQL generated by old ORM versions still running online, so not all queries can be changed freely. The usual approach is to first capture slow SQL and execution plans, use online DDL during off-peak hours to adjust the composite index or add a covering index, and rewrite where and order by so queries can connect to a continuous prefix. The results mostly fall in an experience range where the P95 of target queries drops from seconds to hundreds of milliseconds or tens of milliseconds; but there have also been cases where column order was arranged by what seemed important, yet after launch it still did a full table scan, ultimately requiring rework to rebuild the index and delaying release by half a day to a day. Column order is not decided by guesswork; it must be verified with execution plans and actual traffic.

Applicable and Non-Applicable Boundaries

Cases suitable for adjusting composite index order or adding a single-column index include: table data volume reaches millions of rows or more, slow SQL is concentrated in a few high-frequency queries, condition combinations are relatively stable, the execution plan shows a better prefix is available, and write pressure is acceptable.

Cases that are not applicable or do not need forced changes include: single-table data volume within tens of thousands of rows, and full table scans stable within tens of milliseconds; query conditions are highly dynamic and different for every interface, so a fixed column order is hard to cover; writes are extremely intensive while read latency is acceptable; analytical queries are inherently unsuitable for relying on row-store composite indexes. One-sentence boundary: the goal of column order optimization is to let high-frequency queries form a continuous prefix and reduce the cost of table lookups, not to make every SQL hit an index. If these two points cannot be achieved, maintaining the status quo is often more stable.

FAQ

If the column order in a composite index is changed, will the original single-column query be affected?

Yes. A single-column query can only use an index that has that column as its prefix. If the new order does not start with the original column, the original query may switch to another index or a full table scan, so check the execution plan together before launch.

Why does the query still not hit the composite index even though all fields are present in where?

Common reasons are early truncation by range conditions, mismatched sort columns, columns wrapped by functions or implicit type conversion, or the optimizer giving up because the estimated cost of table lookups is too high. Check the execution plan first before judging.

If there are many queries that use only the last column, must a single-column index be added for every field?

Not necessarily. First count the frequency and returned rows of such queries. Low-frequency queries can accept a full table scan; for high-frequency and high-selectivity queries, consider adding a single-column index or covering index.

Are more columns in a composite index always better?

No. The more columns, the larger the index and the higher the write maintenance cost. According to common practices in 2026, first cover the prefix and sorting of high-frequency queries, then re-evaluate the benefit after 3 to 5 columns.

Does adjusting composite index order require downtime?

Most databases support online DDL, but large tables may still cause lock waits and replication delay. The experience range is to execute during off-peak hours, first observe primary-replica delay and write P95, and then decide whether to continue.

If slow SQL is concentrated in a few queries, first capture the execution plan and do a check with where, order by, returned columns, and table size; if it can be solved by rewriting SQL or adding a covering index, do not rush to rebuild the composite index. If the table is small, writes are intensive, or query patterns vary frequently, maintaining the status quo and accepting full table scans is often the more stable trade-off.