Indexing and Query Performance

The join multiplies each parent row by its number of matching children, and DISTINCT then sorts or hashes that whole intermediate at full row width to undo it. You pay to build rows and pay again to discard them; a semi-join never builds them.

The client driver, not the server, decides a bind parameter's SQL type. If that type does not match the column, the server sees a cross-type comparison and converts the column per row, losing the index — even though the hand-typed literal version was fine.

Comparing two character values requires one agreed collation. When the joined columns declare different collations or character sets, the engine either refuses the comparison or converts one side for every row, which removes that column's index as a way to drive the join.

Anchor on the first row of the current page, flip both the comparison and the ORDER BY, and take the same LIMIT, then reverse those rows in an outer query to restore display order. Flipping only one of the two returns the wrong rows.

OFFSET counts positions in a result recomputed on every request. Rows inserted ahead of the current position push everything down, so the next page repeats rows already shown; deletions pull rows up and skip them. Keyset anchors on a value, so the boundary stays fixed.

Index order only survives if nothing between the scan and the row limit discards it — grouping, DISTINCT, hashing or an ordering key computed later all reintroduce a sort. And even a preserved order reads far past 20 rows when a selective filter rejects most entries.

One statement covering every combination of supplied and omitted filters forces a single plan that must be valid when any parameter is NULL, so no index can be committed to and the engine typically scans. Build the statement from the filters actually supplied, binding values as parameters.

Compare measured plans, not estimates: run both versions with EXPLAIN ANALYZE on the same data and parameters, discard the first cold run, and confirm both queries return identical result sets before trusting any timing difference.

Stop transforming the column: normalize the values on write so the stored form is already canonical, then compare the bare column to a normalized parameter. If the stored data cannot be normalized, the fix moves into the schema — an indexed expression or a persisted generated column — not the WHERE clause.

The star silently grew: the same query now projects kilobytes per row that nobody renders. Payload, server buffers and any buffered sort all widen at once, and engines that store oversized values apart from the row must now fetch them.

No. EXISTS tests only whether the subquery produces a row, so its select list is not evaluated for values. SELECT 1, SELECT * and SELECT NULL all behave identically; the choice is purely stylistic.