SQL Query Performance Analyzer

Queries rarely slow down because the database engine is slow; they slow down because the query structure prevents the optimizer from taking efficient access paths:

• Non-Sargable Predicates: Wrapping columns in scalar functions (e.g. YEAR(created_at) = 2024 or LOWER(email) = ...) forces the engine to evaluate the function on every row, turning an O(log N) index seek into an O(N) full table scan.
• Leading Wildcard LIKE Patterns: Searching with LIKE '%keyword' means the first character is unknown, preventing standard B-tree index traversal.
• Wildcard Projection (SELECT *): Retrieves unneeded columns, saturating network bandwidth, consuming temporary buffer memory, and preventing Index-Only Scans.
• Cartesian Joins (N × M): Joining tables without explicit matching equality conditions produces explosive row multiplication.
• High OFFSET Pagination: OFFSET 50000 forces the engine to read and discard 50,000 rows before returning the requested 20 rows.
• Filesort / In-Memory Sorting: Sorting by unindexed expressions or using ORDER BY RAND() forces temporary table allocations and CPU-intensive sorting.

When building composite indexes across multiple columns, column order in the index definition is critical:

1. Equality Columns First: Columns filtered with exact equality (e.g. status = 'active', tenant_id = 5) should lead the index.
2. Range / Inequality Columns Second: Columns filtered with ranges (e.g. created_at >= '2024-01-01') narrow down the remaining index slice.
3. Sort Columns Third: Columns specified in ORDER BY or GROUP BY allow the engine to retrieve rows in pre-sorted order without a filesort pass.

It is important to understand the boundary between static SQL inspection and actual database execution:

• Static Analysis Detects: Architectural anti-patterns, non-sargable constructs, missing LIMIT boundaries, implicit Cartesian joins, and theoretical indexing opportunities.
• Database Engine Determines: Cost-based plan decisions, disk page reads vs buffer pool cache hits, table cardinality, and index selectivity via statistics collected by ANALYZE TABLE.

Therefore, static analysis provides high-confidence diagnostic guidance, while final optimization should always be verified with execution plan outputs in staging.