Even a perfect schema and rich indexes cannot rescue a poorly written query. Many real-world performance problems come from SQL patterns that force full scans, disable indexes, or do far more work than the application actually needs. A first habit is to avoid `SELECT *`: pulling all columns prevents index-only scans, increases I/O, and sends unnecessary data over the network. Selecting only the columns you need lets covering indexes do their job and shrinks result sets.
Functions and expressions on indexed columns are another common trap. A predicate like `WHERE DATE(created_at) = '2025-01-01'` requires MySQL to apply the function to every row, which usually prevents the use of an index range. Rewriting it as a range — `WHERE created_at >= '2025-01-01 00:00:00' AND created_at < '2025-01-02 00:00:00'` — keeps the index usable. Similar care is needed with implicit type conversions: if the column is an integer but the application passes a string, MySQL may convert values per row and skip the index. Matching data types between literals, parameters, and columns preserves index access. Wildcard LIKE patterns that begin with a leading percent (such as `LIKE '%abc'`) also defeat B-tree prefix indexes; for true substring search on large text columns, FULLTEXT indexes or external search engines are more appropriate than naive LIKE.
Join and subquery design has a major impact as well. Indexed join columns, a minimal number of joined tables, and selective WHERE filters applied as early as possible all reduce the cost of multi-table queries. Correlated subqueries, which reference outer-query columns, may be executed per row and can be much slower than equivalent joins; rewriting them as JOINs or using derived tables often helps. OR conditions across different columns can also prevent a single efficient index from being used; sometimes splitting them into a UNION ALL of simpler queries allows each branch to use a good index. DISTINCT is another frequent source of unnecessary work when it is used to mask a join that produces duplicates; fixing the join is usually better than deduplicating later.
Result-set size and pagination deserve attention too. LIMIT with a selective WHERE clause caps the amount of data scanned and shipped, but large OFFSET values are still expensive because MySQL must scan and discard preceding rows. Keyset pagination — using a WHERE condition on the last seen key (for example, `id > ?`) — is much more index-friendly for deep paging. On the application side, prepared statements and parameterized queries can be parsed and planned once and reused, reducing per-call overhead and stabilizing the plan. Finally, beware of N+1 query patterns, where a loop issues many small queries instead of one larger set-based query; combining those into JOINs or IN lists is almost always faster.