Optimization Questions

Scenario 1: "The query is slow. How do you debug it?"

Answer Approach:

1. Check EXPLAIN Plan: Identify if indexes are being used.
2. Scan Type: Look for "Full Table Scan" (bad) vs "Index Scan" (good).
3. Indexes: Check if filtering columns (WHERE, JOIN) are indexed.
4. Volume: Is the dataset too large? Can we partition it?

Scenario 2: "Improve this query with LIKE"

Query: SELECT * FROM Users WHERE Name LIKE '%John%';

Problem: Leading wildcard % prevents index usage. Solution:

• If looking for 'John' at start: LIKE 'John%' (uses index).
• If full-text search needed: Use Full-Text Index (MySQL/PostgreSQL) output ElasticSearch.

Scenario 3: "UNION vs UNION ALL"

Question: Which is faster? Answer: UNION ALL is faster because it does not check for duplicates. UNION removes duplicates, which is an expensive sorting operation.

Scenario 4: "Why is COUNT(*) fast or slow?"

• MyISAM (MySQL old): Fast (stores row count).
• InnoDB (MySQL new) / PostgreSQL: Slow (scans all rows due to MVCC).
• Optimization: Use an approximate count from system tables if exact number isn't critical.