Performance and Debugging

Introduction

What is it? This chapter is about two habits that separate a beginner from a production engineer:

• Performance: making a query that is already correct also fast. The tools are indexes, EXPLAIN, and writing conditions the database can use an index for.
• Debugging: finding out why a query is wrong (a count that is too big, a NOT IN that returns nothing) by looking at the raw rows, not by guessing.

It also covers stored procedures, a way to save SQL logic inside the database and run it with one call.

Why do we need it? A query that returns the right answer in 5 ms on a test table can take 5 minutes on a table with 50 million rows. And a query that "looks right" can silently return wrong numbers for months. You need a way to see what the database is doing (EXPLAIN), and a way to check the data under the query (inspect the rows).

Real-world analogy Think of a library.

• Finding a book by walking past every shelf is a table scan. Using the catalog to go straight to the right shelf is an index lookup.
• EXPLAIN is asking the librarian, "How will you look for this book?" before you start. EXPLAIN ANALYZE is timing the librarian while they actually do it.
• A stored procedure is a recipe card in a restaurant kitchen. The waiter says "make order 7" and does not describe every step each time.

Business use case An e-commerce dashboard shows "latest 5 orders for this customer" and "number of orders per customer". It is slow at peak time, and finance says the order count does not match the orders table. The fix is an index for the first problem, and a COUNT(DISTINCT ...) for the second.

Dataset Used

An e-commerce database with seven tables: users, products, orders, order_items, payments, payment_status_history, and fraud_blacklist.

CREATE TABLE users (
  user_id BIGINT PRIMARY KEY AUTO_INCREMENT,
  full_name VARCHAR(100) NOT NULL,
  email VARCHAR(255) NOT NULL,
  created_at DATETIME NOT NULL,
  UNIQUE KEY uq_users_email (email)
) ENGINE=InnoDB;

CREATE TABLE products (
  product_id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  price DECIMAL(10,2) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE orders (
  order_id BIGINT PRIMARY KEY,
  user_id BIGINT NOT NULL,
  status ENUM('PLACED','PAID','CANCELLED','REFUNDED','DELIVERED') NOT NULL,
  created_at DATETIME NOT NULL,
  delivered_at DATETIME NULL,
  total_amount DECIMAL(10,2) NOT NULL,
  user_email_cached VARCHAR(255) NULL,
  KEY idx_orders_status (status)
) ENGINE=InnoDB;

CREATE TABLE order_items (
  order_item_id BIGINT PRIMARY KEY AUTO_INCREMENT,
  order_id BIGINT NOT NULL,
  product_id BIGINT NOT NULL,
  qty INT NOT NULL,
  unit_price DECIMAL(10,2) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE payments (
  payment_id BIGINT PRIMARY KEY AUTO_INCREMENT,
  order_id BIGINT NOT NULL,
  currency CHAR(3) NOT NULL, -- 'INR', 'USD'
  amount DECIMAL(10,2) NOT NULL,
  status ENUM('CREATED','AUTHORIZED','CAPTURED','SUCCESS','FAILED','REFUNDED') NOT NULL,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB;

CREATE TABLE payment_status_history (
  psh_id BIGINT PRIMARY KEY AUTO_INCREMENT,
  payment_id BIGINT NOT NULL,
  status ENUM('CREATED','AUTHORIZED','CAPTURED','SUCCESS','FAILED','REFUNDED') NOT NULL,
  status_at DATETIME NOT NULL
) ENGINE=InnoDB;

CREATE TABLE fraud_blacklist (
  user_id BIGINT NULL
) ENGINE=InnoDB;

Two things to notice about the design, because they matter for performance later:

• orders has only its primary key and an index on status. There is no index on user_id or created_at.
• order_items and payments have no index on order_id, and there are no foreign keys. (A foreign key would have created an index automatically. This setup is deliberately "raw" so you can see the slow plans first.)

Insert data

-- The recursive inserts below count up to 5000. MySQL stops a recursive query after
-- 1000 rounds by default, so raise the limit first.
SET SESSION cte_max_recursion_depth = 10000;

INSERT INTO products (name, price) VALUES
('DSA Sheet', 499.00), ('SQL Sheet', 399.00), ('LLD Sheet', 599.00), ('HLD Sheet', 699.00),
('Mock Interview', 1499.00), ('Resume Review', 999.00), ('Premium Notes', 299.00), ('Practice Pack', 199.00),
('System Design Pack', 799.00), ('Aptitude Pack', 149.00), ('CP Pack', 899.00), ('OOP Pack', 349.00),
('Java Pack', 449.00), ('Go Pack', 449.00), ('Python Pack', 449.00), ('Frontend Pack', 449.00),
('Backend Pack', 549.00), ('DB Pack', 399.00), ('Cloud Pack', 649.00), ('Career Pack', 999.00);

INSERT INTO users (full_name, email, created_at) VALUES
('Asha', 'asha@tufdemo.com', '2026-01-01 09:00:00'),
('Ravi', 'ravi@tufdemo.com', '2026-01-02 09:00:00'),
('Neha', 'neha@tufdemo.com', '2026-01-03 09:00:00'),
('Raj', 'raj@tufdemo.com', '2026-01-04 09:00:00');

INSERT INTO users (full_name, email, created_at)
WITH RECURSIVE seq AS (
  SELECT 5 AS n
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 200
)
SELECT
  CONCAT('User', n) AS full_name,
  CONCAT('user', n, '@tufdemo.com') AS email,
  TIMESTAMP('2026-01-01 00:00:00') + INTERVAL (n % 30) DAY + INTERVAL (n % 1440) MINUTE AS created_at
FROM seq;

INSERT INTO orders (order_id, user_id, status, created_at, delivered_at, total_amount, user_email_cached)
WITH RECURSIVE seq AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 5000
)
SELECT
  n AS order_id,
  ((n - 1) % 200) + 1 AS user_id,
  CASE (n % 5)
    WHEN 0 THEN 'PLACED'
    WHEN 1 THEN 'PAID'
    WHEN 2 THEN 'DELIVERED'
    WHEN 3 THEN 'CANCELLED'
    ELSE 'REFUNDED'
  END AS status,
  TIMESTAMP('2026-01-01 00:00:00')
    + INTERVAL (n % 60) DAY
    + INTERVAL (n % 1440) MINUTE AS created_at,
  CASE
    WHEN (n % 5) = 2 THEN (TIMESTAMP('2026-01-01 00:00:00') + INTERVAL (n % 60) DAY + INTERVAL (n % 1440) MINUTE) + INTERVAL (n % 300) MINUTE
    ELSE NULL
  END AS delivered_at,
  ( (n % 20) * 50.0 ) + 199.0 AS total_amount,
  NULL -- keep NULL initially; we will backfill later via Stored Procedure
FROM seq;

INSERT INTO order_items (order_id, product_id, qty, unit_price)
SELECT
  o.order_id,
  ((o.order_id + t.k) % 20) + 1 AS product_id,
  ((o.order_id + t.k) % 3) + 1 AS qty,
  p.price AS unit_price
FROM orders o
JOIN (SELECT 1 AS k UNION ALL SELECT 2 AS k) t
JOIN products p
  ON p.product_id = ((o.order_id + t.k) % 20) + 1;

INSERT INTO payments (order_id, currency, amount, status, created_at)
SELECT
  o.order_id,
  CASE
    WHEN (o.order_id % 10) = 0 THEN 'INR'
    ELSE CASE WHEN (o.order_id % 3) = 0 THEN 'USD' ELSE 'INR' END
  END AS currency,
  o.total_amount AS amount,
  CASE
    WHEN o.status IN ('PAID','DELIVERED') THEN 'SUCCESS'
    WHEN o.status IN ('REFUNDED') THEN 'REFUNDED'
    WHEN o.status IN ('CANCELLED') THEN 'FAILED'
    ELSE 'CREATED'
  END AS status,
  o.created_at + INTERVAL 2 MINUTE AS created_at
FROM orders o;

INSERT INTO payments (order_id, currency, amount, status, created_at)
SELECT
  o.order_id,
  CASE WHEN (o.order_id % 20) = 0 THEN 'USD' ELSE 'INR' END AS currency,
  o.total_amount AS amount,
  CASE
    WHEN o.status IN ('PAID','DELIVERED') THEN 'SUCCESS'
    WHEN o.status IN ('REFUNDED') THEN 'REFUNDED'
    WHEN o.status IN ('CANCELLED') THEN 'FAILED'
    ELSE 'CREATED'
  END AS status,
  o.created_at + INTERVAL 3 MINUTE AS created_at
FROM orders o
WHERE (o.order_id % 10) = 0;

INSERT INTO payment_status_history (payment_id, status, status_at)
SELECT p.payment_id, 'CREATED', p.created_at
FROM payments p;

INSERT INTO payment_status_history (payment_id, status, status_at)
SELECT p.payment_id, 'CAPTURED', p.created_at + INTERVAL 1 MINUTE
FROM payments p
WHERE p.status IN ('SUCCESS','REFUNDED');

INSERT INTO payment_status_history (payment_id, status, status_at)
SELECT p.payment_id, p.status, p.created_at + INTERVAL 2 MINUTE
FROM payments p;

INSERT INTO fraud_blacklist (user_id) VALUES (2), (NULL);

The first line (SET SESSION cte_max_recursion_depth) is an addition. Without it, the orders insert (5000 rounds) stops with a recursion-limit error, because the default limit is 1000.

What the generated data looks like

Order statuses. n % 5 decides the status, so each status has 1000 orders: 0 = PLACED, 1 = PAID, 2 = DELIVERED, 3 = CANCELLED, 4 = REFUNDED.

A useful pattern in the data. User 4's orders are n = 4, 204, 404, ..., 4804, so every one of them has status REFUNDED and total_amount 399.00. User 1's orders are n = 1, 201, 401, ..., 4801, so every one has status PAID and total_amount 249.00. Knowing this lets you predict query results and check them.

The user_email_cached column is deliberately left NULL. It is a copy of the user's email on each order (a convenience column, see the Day 35 notes) that a batch job fills in later.

2. EXPLAIN in MySQL

EXPLAIN shows how MySQL will run a query: which tables it reads, which indexes it uses, and in what order. You use it to find out why a query is slow.

We will use this query throughout. It asks for one user's orders in a date range, newest first:

SELECT order_id, user_id, status, created_at, total_amount
FROM orders
WHERE user_id = 1
  AND created_at >= '2026-01-15'
  AND created_at < '2026-02-15'
ORDER BY created_at DESC
LIMIT 20;

With this data, the query matches 16 orders (user 1's orders fall on 3 different days, and two of those days are inside the range). LIMIT 20 is not reached.

2.1 EXPLAIN FORMAT=TRADITIONAL

Shows the plan as a table with columns such as id, select_type, table, type, possible_keys, key, rows, and Extra.

EXPLAIN FORMAT=TRADITIONAL
SELECT order_id, user_id, status, created_at, total_amount
FROM orders
WHERE user_id = 1
  AND created_at >= '2026-01-15'
  AND created_at < '2026-02-15'
ORDER BY created_at DESC
LIMIT 20;

What to expect before any index exists (the exact numbers differ on your machine):

How to read it

• type = ALL means MySQL plans a full table scan. That can be expensive, but for small tables or queries that need much of the table it can be the optimal access path.
• key = NULL means no index is used.
• rows is MySQL's estimate of how many rows it must examine (about the whole table).
• Using filesort means MySQL needs an extra sorting step because the rows do not already come out in the requested order. The sort happens in memory when the data is small, and can spill to disk when it is large.

Common type values (not a universal best-to-worst ranking)

Common Extra values

2.2 EXPLAIN FORMAT=TREE

Shows the plan as a tree. It is easier to see how the pieces fit together, where filtering happens, and where sorting happens. Child iterators supply rows to their parents on demand; indentation shows the iterator tree, not a guarantee that one subtree fully finishes before all parent work begins.

EXPLAIN FORMAT=TREE
SELECT order_id, user_id, status, created_at, total_amount
FROM orders
WHERE user_id = 1
  AND created_at >= '2026-01-15'
  AND created_at < '2026-02-15'
ORDER BY created_at DESC
LIMIT 20;

The shape of the plan before an index (numbers shown as ...):

-> Limit: 20 row(s)  (cost=... rows=...)
    -> Sort: orders.created_at DESC, limit input to 20 row(s) per chunk  (cost=...)
        -> Filter: ((orders.user_id = 1) and (orders.created_at >= '2026-01-15 00:00:00')
                    and (orders.created_at < '2026-02-15 00:00:00'))  (cost=... rows=...)
            -> Table scan on orders  (cost=... rows=...)

Read it from the inside out: scan the whole table, filter the rows, sort them, then keep the first 20.

2.3 EXPLAIN ANALYZE

EXPLAIN ANALYZE actually runs the query and reports what really happened: the actual time, the actual number of rows, and how many times each step ran (loops). The plain EXPLAIN only shows estimates.

EXPLAIN ANALYZE
SELECT order_id, user_id, status, created_at, total_amount
FROM orders
WHERE user_id = 1
  AND created_at >= '2026-01-15'
  AND created_at < '2026-02-15'
ORDER BY created_at DESC
LIMIT 20;

How to read it

• actual time=a..b reports average timing per loop: a to the first row and b to completion for that iterator. Interpret it together with loops; total work is not represented by reading b in isolation.
• rows= next to it is the real number of rows produced.
• loops=1 shows how many times the step ran. In a join, the inner step can run once per outer row.
• Comparing the estimated rows with the actual rows tells you when MySQL guessed wrong.

Because it runs the query, do not use it carelessly on a very heavy query.

 EXPLAIN           -> "Here is my plan"                (estimates, nothing runs)
 EXPLAIN ANALYZE   -> "Here is my plan AND what happened" (runs the query, real timings)

After adding an index

If we add an index on user_id and created_at, the plan changes.

CREATE INDEX idx_orders_user_created ON orders (user_id, created_at);

EXPLAIN ANALYZE
SELECT order_id, user_id, status, created_at, total_amount
FROM orders
WHERE user_id = 1
  AND created_at >= '2026-01-15'
  AND created_at < '2026-02-15'
ORDER BY created_at DESC
LIMIT 20;

The shape of the plan after the index:

-> Limit: 20 row(s)  (cost=... rows=...) (actual time=... rows=16 loops=1)
    -> Index range scan on orders using idx_orders_user_created
       over (user_id = 1 AND '2026-01-15 00:00:00' <= created_at < '2026-02-15 00:00:00'), (reverse)
       (cost=... rows=...) (actual time=... rows=16 loops=1)

What changed

• The Table scan and the Sort are gone.
• Index range scan shows the query now uses the index to go straight to user 1's rows in the date range, reading only the parts it needs.
• (reverse) means MySQL reads the index backward, so the rows already come out newest first. No separate sort is needed.
• In the original run, the cost dropped to about 7.46 and the actual time was about 0.454 to 0.468 ms, much faster than the earlier plan. Your numbers will differ.

3. What Does an Index Really Do?

An index is a sorted data structure that lets MySQL find rows quickly without scanning the whole table. It works like the index at the back of a book: instead of reading every page to find a topic, you look it up and jump to the right page.

• Faster reads. Indexes drastically reduce the time to find data.
• Slower writes. Every insert, update, or delete must also update the indexes.

 idx_orders_user_created (user_id, created_at)   a sorted list, pointing to rows

 (user 1, 2026-01-02 00:01)  -> order 1
 (user 1, 2026-01-02 10:01)  -> order 601
 (user 1, 2026-01-02 20:01)  -> order 1201
 ...
 (user 1, 2026-01-22 03:21)  -> order 201     <-- a range scan starts here
 (user 1, 2026-01-22 13:21)  -> order 801
 ...
 (user 1, 2026-02-11 06:41)  -> order 401     <-- and stops before Feb 15
 (user 2, 2026-01-03 ...)    -> order 2

Because the list is sorted by user, then by date, MySQL can jump to "user 1, Jan 15" and read forward, instead of looking at 5000 rows.

The access path

When MySQL runs a query, it chooses how to reach the data. This is the access path. There are two main kinds.

1. Table scan (bad)

• This is the default when no useful index exists.
• MySQL reads the entire table and applies the filter to each row.
• Drawback: inefficient, especially on big tables.

2. Index lookup / range scan (good)

• MySQL uses the index to jump directly to the relevant part, skipping irrelevant rows.
• Benefit: much faster, especially when filtering on indexed columns.

Example

SELECT * FROM orders WHERE user_id = 1;

• If user_id is not indexed, MySQL does a table scan and checks each row.
• If user_id is indexed, MySQL does an index lookup and finds the rows much faster.

What is the right index?

The right index is one that fits your query patterns. Three things decide it:

• Filtering. If the query filters on certain columns, an index on them helps.
• Ordering. If you ORDER BY a column, an index on it lets MySQL read rows already sorted.
• Early stop. With an index, MySQL can stop as soon as LIMIT is reached.

-- Ideal index for "one user's orders in a date range, newest first"
CREATE INDEX idx_orders_user_created_at ON orders (user_id, created_at);

(The notes use two names for this index, idx_orders_user_created and idx_orders_user_created_at. They are the same index. Use one name and create it only once.)

Why this index is ideal

• Filter by user_id: the query quickly locates that user's rows.
• Range scan on created_at: within a user, the dates are sorted, so a date range is one continuous slice.
• ORDER BY created_at: the rows are already in date order, so no separate sort is needed.

The order of columns in a composite index matters (extra)

An index on (user_id, created_at) is sorted by user_id first, then by created_at inside each user. That gives the leftmost prefix rule:

A good rule for column order: put columns compared with = first, then the column used for a range or sorting.

Production tip: the cost of indexing

Indexes are great for reads, but they have a cost:

• Storage. Indexes use disk space, sometimes a lot.
• Write cost. Every insert, update, or delete also updates each index, adding overhead.
• Backfill cost. Adding an index to a table that already has data means MySQL must build it for every existing row. On a big table, that is slow and resource-heavy. (In InnoDB, building a secondary index normally lets other queries keep running, but it still uses a lot of CPU and I/O.)

So: do not index everything. Index what your real queries filter, join, and sort on.

4. Sargability

A sargable query (from "Search ARGument ABLE") is one that can use an index effectively. The key idea: keep the indexed column bare, with no function or calculation wrapped around it.

A non-sargable query

EXPLAIN ANALYZE
SELECT COUNT(*)
FROM orders
WHERE user_id = 1
  AND DATE(created_at) = '2026-02-01';

DATE(created_at) makes the query non-sargable. MySQL cannot use the index on created_at to jump to a date, because the index is sorted by the raw created_at value, not by DATE(created_at). It must apply the function to every candidate row, which is slow.

The fix: use a range comparison

EXPLAIN ANALYZE
SELECT COUNT(*)
FROM orders
WHERE user_id = 1
  AND created_at >= '2026-02-01'
  AND created_at < '2026-02-02';

This query is sargable. It compares created_at directly with a range, so it can use the index idx_orders_user_created efficiently. Both queries return the same count; the difference is how much work MySQL does.

About the result. User 1 has no orders on 2026-02-01, so both queries return 0. Try '2026-02-11' and '2026-02-12' instead, and both return 8 (user 1 has 8 orders that day).

 DATE(created_at) = '2026-02-11'          created_at >= '2026-02-11' AND < '2026-02-12'

 compute DATE() for every row             jump to 2026-02-11 in the index
 then compare                             read until 2026-02-12, stop
 SLOW                                     FAST

Common non-sargable patterns and their fixes

If you truly need to filter on an expression often, MySQL 8.0.13 and later can create a functional index, for example CREATE INDEX idx_orders_date ON orders ((DATE(created_at)));. A plain range filter is usually simpler. This is the same lesson as the "Big Performance Rule" in the Dates notes.

5. Debugging a Wrong Count

A query can return a number that looks fine and is still wrong. Let us debug one.

Query 1: counting orders (the wrong way)

SELECT
  o.user_id,
  COUNT(*) AS wrong_order_count
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.user_id = 1
GROUP BY o.user_id;

Output

User 1 has only 25 orders. The count is 50.

The issue: double counting. COUNT(*) counts every row the join produces. Each order has several items, so each order shows up once per item. In this data every order has exactly 2 items, so 25 orders become 50 rows. If one order had five items, it would be counted five times.

 orders (parent)            order_items (child)            joined rows

 order 1   --------------+-- item A                        order 1 | item A
                         +-- item B                        order 1 | item B   <- order 1 twice
 order 201 --------------+-- item C                        order 201 | item C
                         +-- item D                        order 201 | item D <- order 201 twice

 COUNT(*) counts joined rows (50), not orders (25)

This is called join fan-out: joining a parent to its children multiplies the parent rows.

Step 1: Debug by looking at the raw rows

A good first step when debugging is to look at the rows the query works with. Run a plain SELECT to see what the join produces:

SELECT o.user_id, oi.order_id
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.user_id = 1;

Result (a few of the 50 rows):

This shows how many rows come back, and whether the join is duplicating rows. If you see the same order_id several times, that is the sign that duplicates happen because of multiple items per order.

Step 2: Fix the query

We want to count unique orders, so use COUNT(DISTINCT o.order_id):

SELECT
  o.user_id,
  COUNT(DISTINCT o.order_id) AS correct_order_count
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.user_id = 1
GROUP BY o.user_id;

By counting only distinct order_id values, each order is counted once, however many items it has.

Even simpler: do not join if you do not need the child table. If you only need the number of orders, the items are irrelevant:

SELECT user_id, COUNT(*) AS order_count
FROM orders
WHERE user_id = 1
GROUP BY user_id;

It also returns 25 and does less work.

Fan-out also breaks sums (extra). User 1's orders all have total_amount = 249.00.

If you need both order data and item data, aggregate the child table first (in a subquery), and then join the result.

6. Optimizing the Join

Even if a query produces correct results, it may still be inefficient. To debug performance, use EXPLAIN ANALYZE and EXPLAIN FORMAT=TRADITIONAL on the corrected query.

EXPLAIN ANALYZE
SELECT
  o.user_id,
  COUNT(DISTINCT o.order_id) AS correct_order_count
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.user_id = 1
GROUP BY o.user_id;

EXPLAIN FORMAT=TRADITIONAL
SELECT
  o.user_id,
  COUNT(DISTINCT o.order_id) AS correct_order_count
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.user_id = 1
GROUP BY o.user_id;

What you will see. order_items has no index on order_id. Without a supporting index, MySQL may scan order_items, use a hash join, or choose another strategy. Do not infer 25 separate scans of all 10,000 rows without reading the actual iterator plan and its loop counts. The plan shows a scan on order_items (type = ALL).

The fix: index the join column

A table scan alone does not prove an index is needed; scans can be optimal. For this hot join pattern, after confirming row counts, loop counts, selectivity, and workload, an index on the join column is a useful candidate:

CREATE INDEX idx_order_items_order ON order_items (order_id);

After creating the index, run EXPLAIN ANALYZE again. Ideally, MySQL now does an index lookup on order_items for each order, instead of scanning the table. The plan shows type = ref on order_items using idx_order_items_order.

 Before: for each of the 25 orders of user 1 -> scan 10,000 order_items rows
 After:  for each of the 25 orders of user 1 -> index lookup, about 2 rows

A link back to design (Day 35). If order_items.order_id had been declared as a foreign key to orders, MySQL would have created this index automatically. Foreign keys are not only about integrity, they also give you the index for the join.

Always confirm that the result did not change after tuning (still 25).

7. Handling NULL Values

Sometimes NULL values cause surprising behavior, especially with NOT IN and a subquery.

The problem query

SELECT u.user_id, u.email
FROM users u
WHERE u.user_id NOT IN (SELECT user_id FROM fraud_blacklist);

The blacklist contains 2 and a NULL.

Output

Expected: the 199 users that are not blacklisted. Actual: nothing.

Why. For user 1, SQL checks 1 NOT IN (2, NULL), which means 1 <> 2 AND 1 <> NULL.

 1 NOT IN (2, NULL)
 =  1 <> 2      AND   1 <> NULL
 =  TRUE        AND   UNKNOWN
 =  UNKNOWN     ->  the row is NOT returned

A comparison with NULL is UNKNOWN, and one UNKNOWN makes the whole NOT IN UNKNOWN. So if fraud_blacklist contains NULL, no user is returned, and there is no error.

Solution 1: remove the NULLs from the list

SELECT u.user_id, u.email
FROM users u
WHERE u.user_id NOT IN (
  SELECT user_id FROM fraud_blacklist WHERE user_id IS NOT NULL
);

Solution 2: use NOT EXISTS (the safer habit)

SELECT u.user_id, u.email
FROM users u
WHERE NOT EXISTS (
  SELECT 1
  FROM fraud_blacklist fb
  WHERE fb.user_id = u.user_id
);

Both return the 199 users other than user 2. NOT EXISTS never compares a value with NULL directly (the NULL row simply never equals any user id), so a NULL in the data cannot break it.

A third option (extra): an anti-join

SELECT u.user_id, u.email
FROM users u
LEFT JOIN fraud_blacklist fb ON fb.user_id = u.user_id
WHERE fb.user_id IS NULL;

The root cause is also a design smell: a column that holds user ids should probably be NOT NULL. A NULL row in a blacklist has no meaning.

8. A Debugging Workflow (extra)

When a query is wrong or slow, go through the same steps each time.

 1. Is the RESULT right?
      - look at the raw joined rows (SELECT *), check for duplicates and NULL traps
      - compare the number with a simpler query (COUNT on the parent table alone)
        |
        v
 2. Is it FAST enough?
      - EXPLAIN: type, key, rows, Extra
      - EXPLAIN ANALYZE: actual time, actual rows, loops
        |
        v
 3. What is the ACCESS PATH?
      - table scan?  filesort?  a function on an indexed column?
        |
        v
 4. FIX the smallest thing
      - rewrite to be sargable, or add the right index
        |
        v
 5. RE-CHECK
      - same result?  plan better?  writes not hurt?

Deep Dive

Internal behavior. InnoDB stores a table in the order of its primary key, and a secondary index is a separate sorted structure (a B-tree) whose entries point back to the rows. A range scan on a B-tree reads a continuous slice, which is why a sorted composite index can serve both a filter and an ORDER BY. The optimizer chooses the plan using statistics about the table, so its row estimates can be wrong. EXPLAIN ANALYZE shows where they are wrong.

NULL handling (summary)

Performance considerations

• Write the condition so the indexed column stands alone.
• Composite index order: equality columns first, then the range or sort column.
• A LIMIT query is fastest when an index provides the order, so MySQL can stop early.
• Every extra index slows writes and uses space. Remove indexes that no query uses.
• Test with realistic data sizes. A table scan on 200 rows looks fine, and on 50 million rows does not.
• For big backfills, work in batches so locks and transactions stay short.

Stored procedure performance. A procedure is not automatically faster than the same SQL sent from the application. The gain is mainly fewer round trips and shared logic. The queries inside still need good indexes.

Database behavior.

• EXPLAIN FORMAT=TREE and EXPLAIN ANALYZE are available in MySQL 8.0 (ANALYZE from 8.0.18).
• Other databases have their own tools: PostgreSQL uses EXPLAIN (ANALYZE, BUFFERS), and SQL Server has execution plans.
• Stored procedure syntax differs a lot between databases (MySQL, PostgreSQL's PL/pgSQL, SQL Server's T-SQL). Only the idea is portable.
• DELIMITER is a MySQL client command, not part of the SQL language.

Real-world Use Cases

• Dashboards: "latest N records for a user" backed by a composite index, with a procedure that returns them.
• Order workflows: a procedure that marks an order delivered, or refunds an order and its payment in one transaction.
• Batch jobs: backfills, cleanups, and nightly jobs run in small batches.
• Access control: give an app account EXECUTE on a few procedures instead of access to the tables.
• Slow query reviews: take the query from the slow log, run EXPLAIN ANALYZE, and fix the access path.
• Data validation: compare a joined count with the parent table's count to find fan-out bugs.
• Safe filtering: use NOT EXISTS for blacklist, "never ordered", and "not yet paid" checks.

Review Tips

Interviewers commonly ask:

• "What is a stored procedure, and why use one?" — Saved SQL logic run with CALL. It reuses logic, cuts round trips, supports transactions, and lets you grant EXECUTE without table access.
• "What is the difference between DEFINER and INVOKER?" — DEFINER runs with the creator's privileges (the caller needs only EXECUTE). INVOKER runs with the caller's privileges.
• "Why do we use DELIMITER?" — The procedure body contains ;, so the client needs a different end-of-statement marker while you define it.
• "What does EXPLAIN show? What is the difference between EXPLAIN and EXPLAIN ANALYZE?" — The plan with estimates, versus running the query and showing real times and row counts.
• "What do type=ALL and Using filesort mean?" — A full table scan, and an extra sort step.
• "What is a sargable query?" — One that can use an index. Keep the column bare: no function or math on it.
• "Why is WHERE DATE(created_at) = ... slow?" — The function must run for every row, so the index cannot be used. Use a range.
• "How do you choose the right index?" — Look at filters, ordering, and LIMIT. For WHERE user_id = ? AND created_at BETWEEN ... ORDER BY created_at, use (user_id, created_at).
• "What is the leftmost prefix rule?" — A composite index can be used for its first column, or its first two, and so on, but not for a later column alone.
• "What is the cost of an index?" — Storage, slower writes, and build time on existing data.
• "Why does COUNT(*) after a join give too big a number?" — Join fan-out. Use COUNT(DISTINCT ...), avoid the join, or aggregate first.
• "Why does NOT IN return nothing?" — A NULL in the list makes every comparison UNKNOWN. Use NOT EXISTS.

Common Mistakes to Avoid

Mistake 1: Putting a function on an indexed column

Why it happens: DATE(created_at) = '2026-02-01' reads naturally.

Result: the index on created_at is not used, and MySQL computes the function for every row.

Correct: created_at >= '2026-02-01' AND created_at < '2026-02-02'.

Mistake 2: Using COUNT(*) after a one-to-many join

Why it happens: It is the default way to count.

Result: each order is counted once per item (50 instead of 25).

Correct: COUNT(DISTINCT o.order_id), or count from orders alone.

Mistake 3: Trusting a result that "looks right"

Why it happens: A number appears and nothing errors.

Result: wrong numbers in reports.

Correct: Look at the raw rows, and cross-check with a simpler query.

Mistake 4: Using NOT IN with a column that can be NULL

Why it happens: NOT IN reads like plain English.

Result: zero rows, with no error.

Correct: NOT EXISTS, or WHERE col IS NOT NULL inside the subquery.

Mistake 5: Adding indexes without looking at the query

Why it happens: "More indexes means faster."

Result: slower writes and more storage, and sometimes no benefit.

Correct: Design the index from the query's filter, sort, and LIMIT, then check with EXPLAIN.

Mistake 6: Getting the column order of a composite index wrong

Why it happens: Listing columns in any order.

Result: (created_at, user_id) cannot serve "one user, in a date range" as well as (user_id, created_at).

Correct: Equality columns first, then the range or sort column.

Mistake 7: Forgetting to index the join column

Why it happens: The join works on small data.

Result: every outer row scans the inner table.

Correct: Index the join column (or declare the foreign key).

Mistake 8: Reading only the estimates

Why it happens: Plain EXPLAIN is quick.

Result: you miss that the estimate is far from reality.

Correct: Use EXPLAIN ANALYZE and compare estimated and actual rows.

Mistake 9: Forgetting DELIMITER in the command-line client

Why it happens: The same script worked in a GUI tool.

Result: the client cuts the procedure at the first ; and you get a syntax error.

Correct: Wrap the definition in DELIMITER // ... END // ... DELIMITER ;.

Mistake 10: Writing the procedure's privileges wrongly

Why it happens: A typo such as tuf_sql* instead of tuf_sql.*.

Result: the GRANT fails or grants the wrong thing.

Correct: Use db_name.* for "all objects in this database", and GRANT EXECUTE ON db_name.routine_name for one routine.

Mistake 11: Choosing DEFINER security without thinking

Why it happens: It is the default.

Result: callers can do things through the procedure that they could not do directly. If the definer account is dropped, the procedure breaks.

Correct: Choose DEFINER or INVOKER on purpose, and use a dedicated account as the definer.

Mistake 12: Assuming a procedure is atomic by itself

Why it happens: Several statements in one call feels like one unit.

Result: if the second statement fails, the first stays applied.

Correct: Use START TRANSACTION, COMMIT, and an error handler with ROLLBACK.

Mistake 13: Updating a huge table in one statement

Why it happens: It is the simplest code.

Result: long locks and a huge transaction.

Correct: Batch the work (the backfill procedure), keeping each step small.

Mistake 14: Leaving recursion limits at the default when generating test data

Why it happens: The recursive insert works for 200 rows.

Result: the 5000-row insert stops at the 1000-round limit.

Correct: SET SESSION cte_max_recursion_depth = ... before the insert.

Key Takeaways

• A stored procedure is named, saved SQL that you run with CALL. It can take IN and OUT parameters and contain several statements, conditions, loops, and transactions.
• Use DELIMITER in the command-line client so the ; inside the body does not end the definition early.
• CREATE ROUTINE and ALTER ROUTINE let users make and change procedures. EXECUTE lets them run one.
• SQL SECURITY DEFINER runs with the creator's privileges (the caller needs only EXECUTE). SQL SECURITY INVOKER runs with the caller's privileges.
• SHOW CREATE PROCEDURE shows the definition and SHOW PROCEDURE STATUS lists procedures with their definer.
• EXPLAIN shows the plan as a table (FORMAT=TRADITIONAL) or a tree (FORMAT=TREE). EXPLAIN ANALYZE runs the query and shows actual time, rows, and loops.
• type = ALL is a full table scan. Using filesort is an extra sort step. Index range scan is the good sign.
• An index is a sorted structure that turns a scan into a lookup. It speeds up reads, slows down writes, and costs space and build time.
• The right index matches the query: filter columns, then the sort or range column, which also lets LIMIT stop early. A composite index follows the leftmost prefix rule.
• A sargable condition keeps the indexed column bare. DATE(col) = x is not sargable. A range is.
• COUNT(*) after a parent-child join counts child rows. Use COUNT(DISTINCT ...), skip the join, or aggregate first. The same fan-out inflates sums.
• When a result looks wrong, look at the raw rows first.
• NOT IN with a NULL in the list returns nothing. Use NOT EXISTS or filter the NULLs out.
• Always re-check the result and the plan after a fix.