SQL Anti-Pattern Detection¶

QueryAudit detects SQL anti-patterns by analyzing the structure of your queries. This page covers all SQL-related detectors organized by severity, including MySQL-specific patterns, JOIN issues, locking risks, and query structure rules.

ERROR Severity¶

These anti-patterns are logic bugs or guaranteed performance killers. Always fix these.

WHERE Function Usage¶

Why It Matters¶

Wrapping a column in a function inside a WHERE clause disables any index on that column. MySQL cannot use an index to satisfy WHERE DATE(created_at) = '2024-01-01' even if created_at is indexed, because the index stores raw created_at values, not DATE(created_at) values.

Index on created_at:

    2024-01-01 00:00:00
    2024-01-01 08:30:00    <-- Sorted by raw value
    2024-01-01 15:45:22
    2024-01-02 09:00:00

WHERE DATE(created_at) = '2024-01-01'
  --> MySQL must apply DATE() to EVERY row, then compare
  --> Full table scan (index is useless)

Detection¶

QueryAudit parses the WHERE clause and detects function calls that wrap column names. Common functions caught: DATE(), YEAR(), MONTH(), LOWER(), UPPER(), TRIM(), SUBSTRING(), CAST(), and others.

Examples and Fixes¶

-- Bad: index on created_at is useless
SELECT * FROM orders
WHERE DATE(created_at) = '2024-01-01';

-- Good: range query uses the index
SELECT * FROM orders
WHERE created_at >= '2024-01-01 00:00:00'
  AND created_at <  '2024-01-02 00:00:00';

-- Bad
SELECT * FROM orders
WHERE YEAR(created_at) = 2024 AND MONTH(created_at) = 1;

-- Good
SELECT * FROM orders
WHERE created_at >= '2024-01-01' AND created_at < '2024-02-01';

-- Bad
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';

-- Good (option 1): use case-insensitive collation
SELECT * FROM users WHERE email = 'user@example.com';
-- Ensure the column uses utf8mb4_general_ci collation

-- Good (option 2): generated column with index (MySQL 5.7+)
ALTER TABLE users
  ADD email_lower VARCHAR(255) GENERATED ALWAYS AS (LOWER(email)) STORED;
ALTER TABLE users ADD INDEX idx_email_lower (email_lower);

-- Good (option 3): expression index (PostgreSQL)
CREATE INDEX idx_email_lower ON users (LOWER(email));

Report Output¶

[ERROR] Function usage in WHERE clause disables index
  Query  : SELECT * FROM orders WHERE DATE(created_at) = ?
  Table  : orders
  Column : created_at
  Detail : DATE() wrapping disables index on column created_at
  Fix    : Rewrite without function wrapper

Configuration¶

No threshold -- this rule is always active. Suppress per-test if needed:

@QueryAudit(suppress = {"where-function"})

Non-Sargable Expressions¶

Why It Matters¶

When arithmetic operations are applied to an indexed column, MySQL cannot use the index. The term "sargable" comes from Search ARGument ABLE -- the expression must isolate the column on one side for the index to be usable.

Detection¶

QueryAudit detects patterns where a column is wrapped in arithmetic (+, -, *, /) before being compared.

Examples and Fixes¶

-- Bad: index on price is useless
SELECT * FROM products WHERE price + 10 > 100;

-- Good: isolate the column
SELECT * FROM products WHERE price > 90;

-- Bad
SELECT * FROM orders WHERE amount * 2 > 1000;

-- Good
SELECT * FROM orders WHERE amount > 500;

-- Bad
SELECT * FROM metrics WHERE value / 100 = 5;

-- Good
SELECT * FROM metrics WHERE value = 500;

-- Bad: concatenation on column prevents index
SELECT * FROM users WHERE first_name || ' ' || last_name = 'John Doe';

-- Good: use separate conditions or a generated column
SELECT * FROM users WHERE first_name = 'John' AND last_name = 'Doe';

Report Output¶

[ERROR] Arithmetic on column prevents index usage
  Query  : SELECT * FROM products WHERE price + ? > ?
  Table  : products
  Column : price
  Detail : Arithmetic on column 'price' prevents index usage
  Fix    : Move the operation to the other side of the comparison

NULL Comparison Bug¶

Why It Matters¶

In SQL's three-valued logic, comparing anything to NULL with = or != always evaluates to UNKNOWN (not TRUE or FALSE). This means:

• WHERE status = NULL matches zero rows (even rows where status IS NULL)
• WHERE status != NULL also matches zero rows

This is a logic bug, not just a performance issue.

Examples and Fixes¶

-- Bad: always returns 0 rows (logic bug!)
SELECT * FROM users WHERE email = NULL;
SELECT * FROM users WHERE email != NULL;
SELECT * FROM users WHERE email <> NULL;

-- Good: correct NULL comparison
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;

Report Output¶

[ERROR] Comparison with NULL using = or !=
  Query  : SELECT * FROM users WHERE email = NULL
  Table  : users
  Column : email
  Detail : Comparison 'email = NULL' always evaluates to UNKNOWN. Use 'IS NULL' instead.
  Fix    : Change 'email = NULL' to 'email IS NULL'

ORDER BY RAND()¶

Why It Matters¶

ORDER BY RAND() forces MySQL to:

1. Generate a random number for every row in the result set
2. Sort all rows by that random number
3. Return the requested number of rows

This is always a full table scan + full sort, regardless of indexes.

Examples and Fixes¶

-- Full table scan + sort on EVERY execution
SELECT * FROM products ORDER BY RAND() LIMIT 5;

-- Step 1: Get the count
SELECT COUNT(*) FROM products;

-- Step 2: Generate random offset in application
int offset = random.nextInt(count - 5);

-- Step 3: Fetch with calculated offset
SELECT * FROM products LIMIT 5 OFFSET :offset;

-- If IDs are sequential:
SELECT * FROM products
WHERE id >= FLOOR(RAND() * (SELECT MAX(id) FROM products))
ORDER BY id LIMIT 5;

NOT IN Subquery¶

Why It Matters¶

NOT IN (subquery) has a dangerous behavior: if the subquery returns any NULL value, the entire NOT IN condition evaluates to UNKNOWN, returning zero rows.

Examples and Fixes¶

-- Returns ZERO rows if any manager_id is NULL
SELECT * FROM employees
WHERE department_id NOT IN (SELECT manager_id FROM departments);

SELECT * FROM employees e
WHERE NOT EXISTS (
    SELECT 1 FROM departments d
    WHERE d.manager_id = e.department_id
);

SELECT * FROM employees
WHERE department_id NOT IN (
    SELECT manager_id FROM departments WHERE manager_id IS NOT NULL
);

SELECT e.*
FROM employees e
LEFT JOIN departments d ON e.department_id = d.manager_id
WHERE d.manager_id IS NULL;

Cartesian JOIN¶

Why It Matters¶

A Cartesian JOIN (cross join) occurs when a JOIN is missing its ON or USING condition. This produces a result set of rows_A x rows_B, which explodes rapidly:

Table A: 1,000 rows   x   Table B: 1,000 rows   =   1,000,000 rows

Examples and Fixes¶

-- Produces rows_A x rows_B results
SELECT o.id, c.name
FROM orders o
JOIN customers c;  -- missing ON clause!

SELECT o.id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id;

SELECT p.name, s.size
FROM products p
CROSS JOIN sizes s;

FOR UPDATE Without Index¶

Why It Matters¶

SELECT ... FOR UPDATE without an index on the WHERE column causes InnoDB to scan the entire table, acquiring row locks on every row. This effectively locks the entire table.

Examples and Fixes¶

-- Bad: locks entire table if 'status' has no index
SELECT * FROM orders WHERE status = 'pending' FOR UPDATE;

-- Good: ensure index exists
ALTER TABLE orders ADD INDEX idx_status (status);
SELECT * FROM orders WHERE status = 'pending' FOR UPDATE;

WARNING Severity¶

These patterns indicate issues that should be reviewed and typically fixed.

Implicit Type Conversion¶

Why It Matters¶

When a string column is compared to a numeric literal, MySQL performs implicit type conversion on the column, which disables index usage.

Examples and Fixes¶

-- Bad: phone_code is a string column compared to a number
SELECT * FROM users WHERE phone_code = 82;

-- Good: use string comparison
SELECT * FROM users WHERE phone_code = '82';

OR Abuse¶

Why It Matters¶

Excessive OR conditions limit MySQL's ability to use indexes effectively. MySQL can sometimes use Index Merge optimization, but this becomes increasingly unlikely as OR conditions grow.

Examples and Fixes¶

-- Bad: 10 OR conditions
SELECT * FROM products
WHERE category = 'electronics' OR category = 'books' OR category = 'clothing' ...;

-- Good: single IN
SELECT * FROM products
WHERE category IN ('electronics', 'books', 'clothing', 'toys', 'food');

-- Bad: OR across different columns (index merge unlikely)
SELECT * FROM users
WHERE email = 'a@test.com' OR phone = '010-1234' OR username = 'testuser';

-- Good: UNION lets each sub-query use its own index
SELECT * FROM users WHERE email = 'a@test.com'
UNION
SELECT * FROM users WHERE phone = '010-1234'
UNION
SELECT * FROM users WHERE username = 'testuser';

Configuration¶

query-audit:
  or-clause:
    threshold: 3   # default

OFFSET Pagination¶

Why It Matters¶

LIMIT 20 OFFSET 100000 reads and discards 100,000 rows before returning 20. Performance degrades linearly with OFFSET size.

OFFSET 0       -->  Read 20 rows, return 20      (fast)
OFFSET 1,000   -->  Read 1,020 rows, return 20   (ok)
OFFSET 100,000 -->  Read 100,020 rows, return 20  (slow)

Examples and Fixes¶

-- Instead of:
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 100000;

-- Use the last seen ID as a cursor:
SELECT * FROM products WHERE id > 98765 ORDER BY id LIMIT 20;

SELECT p.*
FROM products p
INNER JOIN (
    SELECT id FROM products ORDER BY id LIMIT 20 OFFSET 100000
) AS sub ON p.id = sub.id;

@Query("SELECT p FROM Product p WHERE p.id > :lastId ORDER BY p.id")
List<Product> findNextPage(@Param("lastId") Long lastId, Pageable pageable);

Configuration¶

query-audit:
  offset-pagination:
    threshold: 1000   # default

LIKE Leading Wildcard¶

Why It Matters¶

LIKE '%keyword' prevents B-tree index usage because the search pattern starts with an unknown prefix. MySQL must scan every row.

Examples and Fixes¶

-- Full table scan
SELECT * FROM products WHERE name LIKE '%phone';

SELECT * FROM products WHERE name LIKE 'phone%';

ALTER TABLE products ADD FULLTEXT INDEX ft_name (name);
SELECT * FROM products WHERE MATCH(name) AGAINST('phone');

-- For suffix search, store a reversed copy
ALTER TABLE products ADD name_reversed VARCHAR(255)
  GENERATED ALWAYS AS (REVERSE(name)) STORED;
ALTER TABLE products ADD INDEX idx_name_reversed (name_reversed);

-- Search for names ending in 'phone':
SELECT * FROM products WHERE name_reversed LIKE CONCAT(REVERSE('phone'), '%');

Large IN List¶

Why It Matters¶

Large IN lists cause optimizer overhead and can exceed parser limits. IN lists composed entirely of ? placeholders are automatically skipped (Hibernate IN-clause padding).

Examples and Fixes¶

-- 500 values in IN clause
SELECT * FROM products WHERE id IN (1, 2, 3, ..., 500);

CREATE TEMPORARY TABLE tmp_ids (id BIGINT PRIMARY KEY);
INSERT INTO tmp_ids VALUES (1), (2), (3), ...;
SELECT p.* FROM products p JOIN tmp_ids t ON p.id = t.id;

// Split into multiple queries with smaller IN lists
Lists.partition(ids, 100).forEach(batch -> {
    productRepository.findAllById(batch);
});

Configuration¶

query-audit:
  large-in-list:
    threshold: 100   # WARNING threshold; ERROR at 10x this value

DISTINCT Misuse¶

Why It Matters¶

DISTINCT forces a sort/hash to deduplicate results. When the query context already guarantees uniqueness (e.g., selecting a primary key), DISTINCT adds unnecessary overhead.

Examples and Fixes¶

SELECT DISTINCT * FROM orders WHERE id = 123;

-- Good
SELECT * FROM orders WHERE id = 123;

SELECT DISTINCT o.id FROM orders o JOIN items i ON o.id = i.order_id;

-- Good: fix the query logic
SELECT o.id FROM orders o WHERE EXISTS (
    SELECT 1 FROM items i WHERE i.order_id = o.id
);

HAVING Misuse¶

Why It Matters¶

HAVING is evaluated after grouping. When a condition does not involve an aggregate function, it should be in WHERE (evaluated before grouping) for better performance.

Examples and Fixes¶

-- Bad: non-aggregate condition in HAVING
SELECT department, COUNT(*) FROM employees
GROUP BY department HAVING department = 'Engineering';

-- Good: move to WHERE
SELECT department, COUNT(*) FROM employees
WHERE department = 'Engineering'
GROUP BY department;

Unbounded Result Set¶

Why It Matters¶

A SELECT without LIMIT could return millions of rows, consuming memory and network bandwidth unexpectedly.

Examples and Fixes¶

-- Bad: could return unbounded rows
SELECT * FROM logs WHERE level = 'ERROR';

-- Good: add a LIMIT
SELECT * FROM logs WHERE level = 'ERROR' LIMIT 1000;

-- Good: use pagination
SELECT * FROM logs WHERE level = 'ERROR'
ORDER BY created_at DESC LIMIT 20 OFFSET 0;

Slow Query¶

Detects queries whose execution time exceeds the configured threshold. Configurable via:

query-audit:
  slow-query:
    warning-ms: 500   # WARNING threshold
    error-ms: 3000    # ERROR threshold

CASE in WHERE¶

Why It Matters¶

CASE expressions in WHERE prevent index usage because MySQL cannot predict the output at plan time.

Examples and Fixes¶

-- Bad: CASE in WHERE prevents index
SELECT * FROM orders
WHERE CASE WHEN status = 'active' THEN priority ELSE 0 END > 5;

-- Good: rewrite as explicit conditions
SELECT * FROM orders
WHERE (status = 'active' AND priority > 5);

Correlated Subquery¶

Why It Matters¶

A correlated subquery in the SELECT clause executes once per row of the outer query, similar to the N+1 problem.

Examples and Fixes¶

SELECT o.id,
       (SELECT COUNT(*) FROM items i WHERE i.order_id = o.id) as item_count
FROM orders o;

SELECT o.id, COUNT(i.id) as item_count
FROM orders o
LEFT JOIN items i ON o.id = i.order_id
GROUP BY o.id;

Too Many JOINs¶

Queries with excessive JOINs become exponentially harder for the optimizer and may indicate a design issue. Consider denormalization or materialized views for read-heavy workloads.

Implicit JOIN¶

Why It Matters¶

Comma-separated join syntax makes it easy to accidentally create Cartesian products and is harder to read than explicit JOIN syntax.

Examples and Fixes¶

-- Bad: implicit join (easy to miss the join condition)
SELECT o.id, c.name
FROM orders o, customers c
WHERE o.customer_id = c.id;

-- Good: explicit JOIN
SELECT o.id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id;

Unused JOIN¶

Why It Matters¶

A LEFT JOIN whose table is never referenced in SELECT, WHERE, or ORDER BY adds unnecessary overhead without contributing to the result.

Examples and Fixes¶

-- Bad: 'categories' table is joined but never used
SELECT p.name, p.price
FROM products p
LEFT JOIN categories c ON p.category_id = c.id;

-- Good: remove the unused JOIN
SELECT p.name, p.price
FROM products p;

FOR UPDATE on Non-Unique Index¶

Why It Matters¶

FOR UPDATE on a non-unique index causes InnoDB to acquire gap locks in addition to record locks, potentially blocking inserts and updates on adjacent ranges.

Examples and Fixes¶

-- Bad: gap locks on non-unique index
SELECT * FROM orders WHERE status = 'pending' FOR UPDATE;

-- Good: lock by unique key when possible
SELECT * FROM orders WHERE id = 12345 FOR UPDATE;

Range Lock Risk¶

Range predicates (>, <, BETWEEN) combined with FOR UPDATE on unindexed columns can cause extensive gap locking.

FOR UPDATE Without Timeout¶

Why It Matters¶

FOR UPDATE without NOWAIT or SKIP LOCKED will block indefinitely until the lock is acquired, risking deadlocks and application hangs.

Examples and Fixes¶

-- Bad: blocks indefinitely
SELECT * FROM orders WHERE id = 123 FOR UPDATE;

-- Good: fail immediately if locked
SELECT * FROM orders WHERE id = 123 FOR UPDATE NOWAIT;

-- Good: skip locked rows (for queue-like patterns)
SELECT * FROM orders WHERE status = 'pending'
FOR UPDATE SKIP LOCKED LIMIT 10;

MySQL-Specific Patterns¶

String Concatenation in WHERE¶

String concatenation in WHERE prevents index usage, similar to function wrapping.

-- Bad
SELECT * FROM users WHERE CONCAT(first_name, ' ', last_name) = 'John Doe';

-- Good: use separate conditions
SELECT * FROM users WHERE first_name = 'John' AND last_name = 'Doe';

Function in GROUP BY¶

Function calls in GROUP BY prevent the use of indexes for grouping.

-- Bad: index on created_at cannot be used for grouping
SELECT DATE(created_at) as day, COUNT(*)
FROM orders GROUP BY DATE(created_at);

-- Good: use a generated column (MySQL 5.7+)
ALTER TABLE orders ADD created_date DATE
  GENERATED ALWAYS AS (DATE(created_at)) STORED;
ALTER TABLE orders ADD INDEX idx_created_date (created_date);
SELECT created_date, COUNT(*) FROM orders GROUP BY created_date;

REGEXP Usage¶

REGEXP and RLIKE always require a full table scan -- indexes cannot be used.

-- Bad: full table scan
SELECT * FROM products WHERE name REGEXP '^phone[0-9]+';

-- Good: use LIKE when possible
SELECT * FROM products WHERE name LIKE 'phone%';

FIND_IN_SET¶

FIND_IN_SET indicates comma-separated values stored in a single column, violating First Normal Form (1NF). This prevents indexing and efficient querying.

-- Bad: comma-separated values
SELECT * FROM users WHERE FIND_IN_SET('admin', roles);

-- Good: normalize into a separate table
SELECT u.* FROM users u
JOIN user_roles ur ON u.id = ur.user_id
WHERE ur.role = 'admin';

INFO Severity¶

Best-practice suggestions. These won't fail your build by default.

SELECT *¶

Why It Matters¶

SELECT * fetches every column, wasting bandwidth, memory, and defeating covering indexes.

Examples and Fixes¶

-- Bad
SELECT * FROM orders WHERE status = 'pending';

-- Good
SELECT id, status, total, created_at
FROM orders WHERE status = 'pending';

public interface OrderSummary {
    Long getId();
    String getStatus();
    BigDecimal getTotal();
}

@Query("SELECT o.id, o.status, o.total FROM Order o WHERE o.status = :status")
List<OrderSummary> findSummaryByStatus(@Param("status") String status);

Redundant Filter¶

Detects duplicate predicates in WHERE clause.

-- Bad: redundant condition
SELECT * FROM users WHERE status = 'active' AND status = 'active';

-- Good: remove duplicate
SELECT * FROM users WHERE status = 'active';

COUNT vs EXISTS¶

Why It Matters¶

COUNT(*) must scan all matching rows, while EXISTS can stop after finding the first match.

SELECT CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END FROM orders WHERE user_id = ?;

SELECT EXISTS(SELECT 1 FROM orders WHERE user_id = ?);

// Bad: counts all matching rows
long count = orderRepository.countByUserId(userId);
boolean exists = count > 0;

// Good: stops at first match
boolean exists = orderRepository.existsByUserId(userId);

UNION Without ALL¶

UNION forces a sort to deduplicate results. If duplicates are acceptable or impossible, use UNION ALL.

-- Bad: unnecessary deduplication sort
SELECT id FROM active_users UNION SELECT id FROM premium_users;

-- Good: skip dedup when not needed
SELECT id FROM active_users UNION ALL SELECT id FROM premium_users;

COUNT(*) Without WHERE¶

COUNT(*) without WHERE scans the entire table. In InnoDB, this is always a full scan (unlike MyISAM which stores the count).

-- Slow on large InnoDB tables
SELECT COUNT(*) FROM orders;

-- Consider: cached counter, approximate count, or filtered count
SELECT COUNT(*) FROM orders WHERE status = 'active';

Excessive Column Fetch¶

Flags queries that fetch too many columns. Consider using DTO projection to select only the columns you need.

-- Bad: fetching 30 columns when you only need 3
SELECT * FROM user_profiles WHERE user_id = ?;

-- Good: select only needed columns
SELECT display_name, avatar_url, bio FROM user_profiles WHERE user_id = ?;

Mergeable Queries¶

Detects multiple simple queries to the same table that could be merged into one.

// Bad: 3 separate queries
User user = userRepository.findById(1L);
User user2 = userRepository.findById(2L);
User user3 = userRepository.findById(3L);

// Good: single query
List<User> users = userRepository.findAllById(List.of(1L, 2L, 3L));

Non-Deterministic Pagination¶

ORDER BY + LIMIT on a non-unique column produces inconsistent pagination results.

-- Bad: created_at may have duplicates, page boundaries shift
SELECT * FROM products ORDER BY created_at LIMIT 20 OFFSET 40;

-- Good: add a tiebreaker column
SELECT * FROM products ORDER BY created_at, id LIMIT 20 OFFSET 40;

Force Index Hint¶

FORCE INDEX, USE INDEX, and IGNORE INDEX hints override the optimizer. These may become stale as the schema evolves and should be reviewed periodically.

-- Flagged: optimizer hint may become stale
SELECT * FROM orders FORCE INDEX (idx_status) WHERE status = 'pending';

-- Better: let the optimizer decide, ensure proper indexes exist
SELECT * FROM orders WHERE status = 'pending';

LIMIT Without ORDER BY¶

Why It Matters¶

LIMIT without ORDER BY returns an arbitrary subset of rows. The result is non-deterministic and may vary between executions.

-- Bad: which 10 rows?
SELECT * FROM users LIMIT 10;

-- Good: deterministic ordering
SELECT * FROM users ORDER BY id LIMIT 10;

Window Function Without PARTITION BY¶

Window functions without PARTITION BY operate over the entire result set, which may indicate a missing partition clause.

-- Flagged: ROW_NUMBER over entire table
SELECT id, ROW_NUMBER() OVER (ORDER BY created_at) as rn FROM orders;

-- If you intended per-customer numbering:
SELECT id, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at) as rn
FROM orders;

Summary Table¶

Related Pages¶

• Missing Index Detection -- Index-specific detectors
• DML Anti-Patterns -- INSERT/UPDATE/DELETE patterns
• N+1 Query Detection -- Repeated query patterns
• Detection Rules Overview -- Complete reference table