查询字段包含条件判断时，是否需要创建索引？

Great question—let’s cut through the confusion here, starting with your initial rule of thumb (which is totally a solid starting point!).

First, let’s clarify the core difference between what happens in the WHERE clause vs. a conditional in the SELECT clause:

• The WHERE clause is all about filtering rows before any data is returned. When you run SELECT CustomerName FROM Orders WHERE CustomerID=10, the database uses the CustomerID index to quickly locate only the rows where CustomerID=10—this reduces the number of rows it has to process, which is why the index matters here.
• A conditional like IF(CustomerSalution IS NULL, ...) in the SELECT clause runs after the database has already fetched the rows that match your WHERE criteria. It’s just transforming the data in those already-selected rows to shape your final result set. This step doesn’t affect how the database finds or filters rows in the first place.

Let’s use a concrete example to make this tangible:
Suppose you run:

SELECT 
  IF(CustomerSalution IS NULL, 'Dear', CustomerSalution) AS Greeting,
  CustomerName
FROM Orders 
WHERE CustomerID=10;

Here’s what the database does step-by-step:

1. Uses the CustomerID index to find all rows where CustomerID=10 (fast, thanks to the index).
2. For each of those rows, it checks the CustomerSalution value and applies the IF logic to create the Greeting column.
3. Returns the Greeting and CustomerName columns for those rows.

The IF check here doesn’t require an index on CustomerSalution because the database is already working with a small, filtered set of rows. The index wouldn’t speed up this post-filtering transformation step at all.

When might you need an index on a field used in a SELECT conditional? #

There’s one edge case: if you’re using that transformed value in other operations that do affect row retrieval or sorting. For example:

• If you ORDER BY IF(CustomerSalution IS NULL, 'Dear', CustomerSalution), the database might benefit from an index on the calculated value (many databases support computed column indexes for this scenario).
• Or if you’re joining on that transformed value (though that’s less common).

But in your original scenario—just using the conditional to shape the output columns—no index is needed for the field in the IF statement.

Quick recap #

• WHERE clause fields need indexes because they determine which rows the database has to process.
• SELECT clause conditionals are post-processing steps; they don’t impact row selection, so the field in the conditional doesn’t need an index unless it’s used in other index-reliant operations.

内容的提问来源于stack exchange，提问作者user3649739