The way a query is written often matters more than the configuration surrounding it. SELECT * is a common performance anti-pattern because it reads every column, preventing index-only scans and transferring unnecessary data across the network. Selecting only the columns actually needed allows the query to use a covering index and reduces both I/O and serialization cost. Similarly, applying functions to indexed columns—such as WHERE DATE(created_at) = '...'—prevents MySQL from using a normal index range, because the function must be evaluated against every row; rewriting the predicate as a range like WHERE created_at >= '...' AND created_at < '...' restores index usage. The same logic applies to implicit type conversions: when MySQL converts indexed values to match a parameter, the index becomes unusable. Keeping parameter types aligned with column types—and casting in the application rather than in SQL—avoids this trap. Heavy string manipulation in SQL is generally less efficient than doing it in application code, where libraries are usually more capable.
JOINs and subqueries deserve careful construction. INNER JOIN is usually cheaper than LEFT JOIN because it does not retain unmatched rows, so an unnecessary LEFT JOIN can force MySQL to process more data than the application actually needs. The cost of any join can be reduced by indexing join columns, filtering as early as possible with selective WHERE conditions, and minimizing the number of tables involved. Subqueries also need attention: a correlated subquery runs once per outer row, often causing serious slowdown, while a non-correlated subquery runs only once; rewriting a correlated subquery as a JOIN or a derived table is frequently much faster. A classic related problem is the N+1 query pattern, where application code issues many small queries in a loop instead of one set-based query, multiplying round-trip overhead; the fix is to use joins or IN lists that fetch all needed data in fewer, larger queries. SELECT ... FOR UPDATE is similarly expensive in hot code paths because it acquires row locks even for reads, increasing contention; when possible, optimistic concurrency with version columns and conditional UPDATE or INSERT provides a cheaper alternative.
ORDER BY, LIMIT, and pagination introduce their own considerations. To avoid filesort, the columns in ORDER BY should match an index in the same order and direction, and the query should not mix incompatible expressions. Large OFFSET values are surprisingly expensive because MySQL must still scan and discard all preceding rows; keyset pagination, which uses an indexed condition like id > ? based on the last seen value, scales much better for deep paging. LIMIT combined with selective WHERE clauses reduces rows scanned, processed, and sent to the client. Wildcards at the start of LIKE patterns (LIKE '%abc') prevent normal prefix index usage, while full-text search needs dedicated FULLTEXT indexes or external search engines. Window functions require additional sorting or buffering per partition, so limiting partition and ORDER BY scope and supporting them with indexes helps. DISTINCT is often misused to mask duplicate-creating joins; fixing the join logic is preferable to relying on DISTINCT for deduplication. CTEs (WITH clauses) make complex queries clearer but can hurt performance when misused—non-materialized or recursive CTEs may be re-evaluated repeatedly, and large materialized CTEs can create heavy temp tables—so comparing EXPLAIN plans with equivalent derived tables is a good verification step. Prepared statements and parameterized queries amortize parsing and planning costs across many executions and improve plan cache efficiency. Finally, monitoring rows_examined versus rows_sent helps catch queries that scan far more rows than they return, a strong signal of poor selectivity or missing indexes; ORM-generated SQL often produces such patterns, so profiling real queries and rewriting critical paths yields large gains.