Analyseur de Requêtes SQL

Detecting SQL Anti-Patterns and Performance Issues

SQL queries that produce correct results can still devastate database performance. A SELECT without a WHERE clause scans every row in the table, a cartesian product from a missing JOIN condition multiplies result sets exponentially, and functions applied to indexed columns prevent index usage — turning millisecond queries into minute-long operations. The SQL Query Analyzer detects these anti-patterns statically by parsing your SQL without requiring database access, scoring query complexity and flagging operations that will degrade performance as data volumes grow.

Paste any SQL query to receive anti-pattern detection covering full table scans, cartesian products, missing join conditions, index-killing function usage, subquery patterns, and overall complexity scoring. Each finding explains why the pattern is problematic, estimates the performance impact at scale, and suggests optimized alternatives. All analysis happens client-side using Web Workers for complex queries.

Full Table Scan Detection

The most impactful anti-pattern is querying without filtering — forcing the database to read every row:

• SELECT without WHERE: On tables with millions of rows, this returns the entire dataset regardless of whether the application needs all of it
• WHERE with functions on columns: WHERE YEAR(created_at) = 2024 prevents index usage on created_at — the database must evaluate the function for every row
• Implicit type conversions: WHERE id = '123' when id is an integer forces type conversion per row, bypassing indexes
• LIKE with leading wildcard: WHERE name LIKE '%smith' cannot use a B-tree index and requires full scan

The analyzer identifies these patterns and suggests alternatives: range conditions for date columns, explicit casts, and prefix-only LIKE patterns that utilize indexes.

Join Quality and Cartesian Products

Incorrect joins are the most common cause of exponential query execution time:

• Missing ON clause: A JOIN without a condition produces a cartesian product — if both tables have 1000 rows, the result has 1,000,000 rows
• Cross join detection: Explicit or implicit CROSS JOINs that may be unintentional
• Join on non-indexed columns: Joining on columns without indexes forces nested-loop scans
• Excessive joins: Queries joining more than 5-6 tables become difficult to optimize

Complexity Scoring

The analyzer assigns an overall complexity score based on multiple factors:

• Table count: Each additional table increases optimizer complexity
• Subquery depth: Correlated subqueries execute once per outer row
• Aggregate functions: GROUP BY with HAVING adds sorting and filtering passes
• DISTINCT usage: Forces deduplication which may require temporary tables
• UNION operations: Concatenate and deduplicate result sets
• ORDER BY on non-indexed columns: Requires filesort operations

Scores range from 1 (trivial single-table lookup) to 10 (complex multi-join with subqueries), helping teams prioritize optimization efforts on the most impactful queries.

Code Examples

Query with Anti-Patterns Detected

-- Original query with issues:
SELECT *
FROM orders o, customers c, products p
WHERE YEAR(o.created_at) = 2024
  AND o.customer_id = c.id
  -- Missing: AND o.product_id = p.id  (cartesian with products!)
ORDER BY o.total DESC;

-- Analyzer findings:
-- 1. SELECT * — fetches all columns including unnecessary data
-- 2. YEAR(created_at) — function on indexed column prevents index usage
-- 3. Cartesian product: 'products' table has no join condition
-- 4. ORDER BY on potentially non-indexed column
-- Complexity score: 8/10

-- Suggested fix:
SELECT o.id, o.total, c.name, p.title
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN products p ON o.product_id = p.id
WHERE o.created_at >= '2024-01-01'
  AND o.created_at < '2025-01-01'
ORDER BY o.total DESC
LIMIT 100;