WHERE, Projection & Sargable Queries

The list query that ignored the index you just built

GET /api/orgs/:orgId/tickets?status=open&updated_after=2026-01-01

Article 03 left you with a solid composite. Then the handler does something like this:

Ticket::query()
    ->where('org_id', $orgId)
    ->where('status', 'open')
    ->whereDate('updated_at', '>=', '2026-01-01') // looks friendly
    ->get(); // SELECT *

Eloquent means well. whereDate wraps the column. get() asks for every column, including a fat body TEXT you never show in the inbox table. Same product filters. Worse plan. More bytes than the UI needs.

The fixed shape keeps the API contract and stops fighting the index:

Ticket::query()
    ->select(['id', 'public_id', 'status', 'assignee_id', 'subject', 'updated_at'])
    ->where('org_id', $orgId)
    ->where('status', 'open')
    ->where('updated_at', '>=', '2026-01-01')
    ->limit(50)
    ->get();

SELECT id, public_id, status, assignee_id, subject, updated_at
FROM tickets
WHERE org_id = ?
  AND status = 'open'
  AND updated_at >= ?
LIMIT 50;

(ORDER BY belongs in the pagination article, coming later in the series. I'm leaving it off on purpose.)

This is post four of Learn MySQL. Types shaped the columns. The primary key clustered the table. Composites gave list endpoints a path. Today is about your WHERE and select list. Do they let that path get used?

Filter × project

Every SELECT answers two questions: which rows survive, and which columns leave the server?[1][2] Apps feel both. Filter work is pages read while finding matches. Projection work is bytes copied, shipped over the network, and parked in the buffer pool. You hauled a wide row for a skinny card UI.

InnoDB still finds rows the way articles 02 and 03 described. Clustered descent for WHERE id = ?. Secondary descent plus bounce for org/status/date filters.[3] This article is about two traps. Do not disable that machinery with a friendly-looking function. Do not ask for the novel when you needed the headline.

A WHERE the optimizer can actually use

Sargable means the engine can compare an indexed column to a known constant or range. It does not transform every row's column value first. Once you know the term, "index-friendly" says the same thing.

Range-eligible shapes on a B-tree include equality, IN, IS NULL, inequalities, BETWEEN, and LIKE 'prefix%' when the pattern is a constant that does not start with %.[4] Leading wildcards (LIKE '%refund%') are not a range driver on that column. Wrapping the column in YEAR(), DATE(), LOWER(), or arithmetic usually isn't either.

The function rule is worth thirty seconds of why. It looks like optimizer pettiness. It isn't. The index stores updated_at values, sorted. It does not store YEAR(updated_at) anywhere. A B-tree can seek to "the first entry ≥ 2026-01-01" because that's a position in the sort order. It cannot seek to "entries whose year is 2026" without computing the function on values it hasn't read yet. So MySQL reads them all. Rewrite the predicate as a range on the stored value. Then the same sorted tree answers the same product question at index speed.

Constants on the right are fine. WHERE id = POW(2, 3) can still be a point lookup after constant folding. Nondeterministic junk like WHERE id = FLOOR(1 + RAND() * 49) cannot.[5]

One nuance before you poke the demo. Wrap the date in YEAR(updated_at) on a composite that already has org_id and status equalities. Those two parts often still show ref. Only the date column freezes. Flip the switch and watch the beam grind through every open leaf instead of seeking straight to the 2026 range. That grind is the footgun, not a full table scan. It's the same idea as article 03's leftmost prefix freeze: the predicates decide how much of a fixed index lights up.

Those ref / range / ALL labels are teaching shorthand, not a promise from EXPLAIN. Steal the rewrite table:

Functions on columns (Eloquent edition)

whereDate, whereYear, and friends read like product language. They compile to functions on the column. Prefer:

->where('updated_at', '>=', $start)
->where('updated_at', '<', $end)

For email lookups, don't LOWER(email) on every request if utf8mb4_0900_ai_ci already gives you case-insensitive equality (house default from article 01). Canonicalize on write when you need a stricter rule. Generated columns and functional indexes exist; that rabbit hole comes later in the series. For now: bare column on the left, bind param on the right.

Projection: stop selecting the novel

SELECT * is fine in a scratch session. In a list handler it couples your JSON to every future column. It also hauls TEXT / JSON / secrets you never render.[6][7] Wider rows mean fewer rows per InnoDB page. So the same filter touches more I/O even when the index match is perfect.

List endpoints want card columns. Detail endpoints can ask for body. Same table, two jobs. Watch both lanes haul pages for the same LIMIT 50. The fat one keeps fetching:

If the select list ever fits entirely inside a secondary index, MySQL may answer from the index without bouncing to the clustered row. That is a covering index.[3][8] Designing for it gets a whole article later in the series. Today, listing columns keeps that door open. It also stops you shipping kilobytes of body to an inbox table.

List vs detail

Detail can afford a wider row. List should not silently be detail. In Eloquent that means select(...) / toApiArray() driven by the DTO, not Ticket::all() vibes with a where tacked on.

Prefix length for this query

Article 03 taught the leftmost-prefix rule for the index you build. Here the index is fixed and the predicates move. Equalities on the left parts, then one range. Skip a middle column and the walk stops there.[4] The sargability switch above is the same story from the query side. Wrap the trailing column and that segment goes dark even though org_id and status still light.

When you peek at EXPLAIN later, key_len is the rough "how much of this key did we use?" number. Fluency comes later in the series. For now, if your filters don't light the prefix you expected, fix the WHERE before you add another index.

Index condition pushdown (quick)

Sometimes MySQL can test extra conditions on columns that appear in the index before it reads the full clustered row. The docs' classic shape is an index on (zipcode, lastname, …) with zipcode = ? driving access and an awkward lastname LIKE '%…%' checked on the index tuple when possible. A filter on address still needs the row.[9]

In EXPLAIN, Using index condition means that pushdown happened. It is not the same as Using index (covering / index-only). ICP is a consolation prize that reduces row reads. Covering is the design where you needed fewer row reads in the first place. We'll design for covering when that article lands.

Eloquent patterns

The footgun gallery:

// Bad: function on column + star projection
Ticket::where('org_id', $orgId)
    ->whereYear('updated_at', 2026)
    ->get();

// Better: bare column, explicit list columns
Ticket::query()
    ->select(['id', 'public_id', 'status', 'assignee_id', 'subject', 'updated_at'])
    ->where('org_id', $orgId)
    ->where('status', 'open')
    ->where('updated_at', '>=', $start)
    ->where('updated_at', '<', $end)
    ->limit(50)
    ->get();

Log SQL in local (DB::listen / Telescope). If you see date(updated_at) or a naked select *, fix the builder, not the server.

Unsupported filter combos on a public API can be a 400. A table scan is not a product feature.

EXPLAIN previews (not fluency)

On staging data that looks like prod:

EXPLAIN
SELECT id, public_id, status, subject, updated_at
FROM tickets
WHERE org_id = 42
  AND status = 'open'
  AND updated_at >= '2026-01-01';

On a healthy tickets table with idx_org_status_updated (org_id, status, updated_at), a tabular EXPLAIN often looks roughly like this (columns trimmed):

Read it left to right. type = range is a friend. key names the composite you meant. key_len says how much of that key participated. rows is an estimate. Extra here shows index condition pushdown, not covering (covering would say Using index).[10][11] Contrast that with the bad list below (YEAR(updated_at)). You may still see a ref on org_id/status with a shorter key_len and no useful range on the date part. We'll read these properly in the EXPLAIN article. If key is NULL after you added what you thought was the perfect index, check for functions on columns before you assume MySQL is haunted.

ANALYZE TABLE keeps stats from lying after big loads. That's the one ops sentence you get today.[12]

Worked schema (tickets, continued)

CREATE TABLE tickets (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id     CHAR(26) NOT NULL,
  org_id        BIGINT UNSIGNED NOT NULL,
  status        ENUM('open','pending','resolved') NOT NULL,
  assignee_id   BIGINT UNSIGNED NULL,
  subject       VARCHAR(200) NOT NULL,
  body          TEXT NULL,
  updated_at    DATETIME(6) NOT NULL,
  created_at    DATETIME(6) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_tickets_public_id (public_id),
  KEY idx_org_status_updated (org_id, status, updated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Detail:

SELECT id, public_id, org_id, status, assignee_id, subject, body,
       updated_at, created_at
FROM tickets
WHERE id = ?;

List (good):

SELECT id, public_id, status, assignee_id, subject, updated_at
FROM tickets
WHERE org_id = ?
  AND status = ?
  AND updated_at >= ?
LIMIT 50;

List (common mess):

SELECT *
FROM tickets
WHERE org_id = ?
  AND status = ?
  AND YEAR(updated_at) = ?;

Checklist before you merge the handler

• Detail by id uses PK equality (or a unique secondary you meant); list is not secretly detail
• List filters are sargable: bare column on the left, constants/binds on the right
• whereDate / whereYear / LOWER(column) got rewritten or justified
• Composite leftmost prefix matches the filters you send (article 03)
• Projection is intentional: no SELECT * on list endpoints; body stays out
• OR / q= search has a real strategy, not hope
• You glanced at EXPLAIN key/key_len on prod-shaped data
• You did not "fix" a bad WHERE with LIMIT alone (pagination semantics get their own article)

A perfect filter still hurts if you ORDER BY something unindexed or OFFSET into oblivion. That's where the series goes next: sorting, LIMIT, and pagination, then joins that multiply rows. Those articles are in the works; the series page is where they'll appear.

References

1. [1]MySQL 9.7 Reference Manual — SELECT Statement
2. [2]MySQL 9.7 Reference Manual — Selecting Rows / Selecting Columns
3. [3]MySQL 9.7 Reference Manual — How MySQL Uses Indexes
4. [4]MySQL 9.7 Reference Manual — Range Optimization
5. [5]MySQL 9.7 Reference Manual — Function Call Optimization
6. [6]MySQL 9.7 Reference Manual — Selecting All
7. [7]MySQL 9.7 Reference Manual — Optimizing SELECT Statements
8. [8]MySQL 9.7 Reference Manual — Optimizing InnoDB Queries
9. [9]MySQL 9.7 Reference Manual — Index Condition Pushdown Optimization
10. [10]MySQL 9.7 Reference Manual — WHERE Clause Optimization
11. [11]MySQL 9.7 Reference Manual — Verifying Index Usage
12. [12]MySQL 9.7 Reference Manual — CREATE INDEX Statement