The SQL Mistake Everyone Makes: Writing vs Execution Order

Prashant Patil — Mon, 06 Apr 2026 18:25:40 +0000

When I first started learning SQL, my approach was very straightforward. I would begin with the SELECT statement, list the columns I wanted, add the FROM clause, and then shape the query based on the problem.

This worked well for simple queries. However, as problems became more complex, I repeatedly ran into the same issue: I had to rewrite queries from scratch because of small mistakes. This wasted time and slowed down my progress.

The Problem

The core issue was simple — I was writing SQL in the order it appears, not in the order it executes.

Because of this, I:

Applied filters at the wrong stage
Misused aggregate functions
Got incorrect results
Rewrote queries multiple times

The Turning Point

While watching a developer solve multiple SQL problems, I noticed a pattern:

They rarely rewrote queries
They followed a structured flow
They thought in terms of execution

That is when I realized:

SQL is not executed in the order we write it.

Understanding this changed everything.

SQL Execution Order

The actual execution order of SQL is:

FROM
WHERE
GROUP BY
HAVING
SELECT
DISTINCT
ORDER BY
LIMIT

Each step transforms the data before passing it to the next stage.

Clause Breakdown

FROM

Selects the base table and performs joins.

WHERE

Filters rows before grouping.

GROUP BY

Groups rows based on column values.

HAVING

Filters grouped data.

SELECT

Chooses columns and applies calculations.

DISTINCT

Removes duplicates.

ORDER BY

Sorts results.

LIMIT

Restricts number of rows returned.

Example

Table: orders

order_id	customer	amount	city
1	A	100	Pune
2	B	200	Mumbai
3	A	150	Pune
4	C	300	Delhi
5	B	250	Mumbai

Problem

Find total order amount per city where total > 200 and sort descending.

Query

SELECT city, SUM(amount) AS total_amount
FROM orders
WHERE amount > 100
GROUP BY city
HAVING SUM(amount) > 200
ORDER BY total_amount DESC;

Execution Walkthrough

Step 1: FROM

All rows are selected.

Step 2: WHERE

Filter amount > 100.

Remaining rows:

Mumbai: 200, 250
Pune: 150
Delhi: 300

Step 3: GROUP BY

Groups formed:

Mumbai
Pune
Delhi

Step 4: HAVING

Filter groups:

Mumbai (450) -> keep
Pune (150) -> remove
Delhi (300) -> keep

Step 5: SELECT

Result:
| city | total_amount |
|--------|-------------|
| Mumbai | 450 |
| Delhi | 300 |

Step 6: ORDER BY

Sorted descending:

Mumbai
Delhi

Key Takeaway

Do not think in terms of writing SQL.

Think in terms of execution.

This will:

Reduce errors
Improve speed
Help solve complex queries

Conclusion

Understanding SQL execution order is a fundamental skill. Once you start thinking in execution steps instead of syntax order, your ability to write efficient and correct queries improves significantly.

Why Deleting `node_modules` Takes Forever on Windows

Prashant Patil — Sat, 21 Mar 2026 16:56:18 +0000

A few days ago, I was deleting a project when something unexpectedly frustrating happened — it took way too long.

Not seconds. Not even a minute. It just kept going.

If you’ve worked with JavaScript projects, you already know the culprit: node_modules.

That moment got me thinking. Since I’ve been studying operating system fundamentals, I wanted to understand what’s actually happening behind the scenes. Why does a simple delete operation take so long?

So I went down the rabbit hole — digging through documentation, GitHub discussions, and system-level explanations. I even explored how Linux handles file deletion to compare behaviors.

Here’s what I found.

The Real Reason Isn’t Just “Too Many Files”

Yes, node_modules contains thousands of files — sometimes hundreds of thousands.

But that’s only part of the story.

The real reason deletion is slow on Windows comes down to how the operating system handles:

File system operations
Security checks
Path resolution
File locking

Let’s break it down.

1. Windows Deletes Files One by One

Unlike what many people assume, Windows does not delete folders in bulk.

Instead, it:

Traverses every directory
Deletes each file individually
Updates metadata for every operation

Now imagine doing that for 100,000+ files.

That alone creates noticeable delay.

2. File Locking Adds Extra Overhead

Windows uses a strict file locking mechanism.

Before deleting a file, it:

Checks whether the file is in use
Verifies parent directories
Temporarily locks parts of the directory structure

If anything is being accessed — even indirectly — the system slows down or retries the operation.

This adds friction, especially in deeply nested folders like node_modules.

3. Windows Defender Is the Biggest Bottleneck

This is where most of the time actually goes.

Windows Defender scans files not just when they’re created or opened — but also when they’re deleted.

That might sound unnecessary, but there’s a reason.

Malware can:

Create harmful files
Execute them
Delete them quickly to avoid detection

To prevent this, Windows scans files during deletion as well.

In many cases, 60–70% of the total deletion time is spent on Defender scans alone.

4. Long File Paths Make Things Worse

node_modules often contains deeply nested dependencies.

This leads to extremely long file paths.

Windows has historically had a path length limit (~260 characters). Even though modern systems support longer paths, many tools still struggle with them.

When paths exceed limits:

Operations can fail
Windows retries them silently
Additional time is wasted

5. NTFS File System Overhead

Windows uses the NTFS file system, which prioritizes reliability and consistency.

For every file deletion, NTFS:

Updates logs
Maintains metadata
Ensures file system integrity

Doing this for thousands of files significantly slows things down.

How Linux and macOS Handle It Differently

Unix-based systems take a much simpler and faster approach.

They:

Avoid heavy antivirus scanning during deletion
Use more efficient file unlinking mechanisms
Have fewer restrictions on file paths
Use a more flexible file locking model

The result is a noticeable difference in performance.

Quick Comparison

Aspect	Windows (NTFS)	Linux/macOS (ext4/APFS)
File Deletion	File-by-file with checks	Faster unlinking
Antivirus	Scans during deletion	Typically no scan
Path Limits	Historically restrictive	More flexible
File Locking	Strict	More relaxed
Performance on `node_modules`	Slow	Significantly faster

Why Windows Prioritizes Safety

At this point, it’s fair to ask: why does Windows do all this?

The answer is simple — security.

Without these checks:

Malware could create and delete files instantly
Those files might never be scanned
The system could be compromised silently

So while it feels inefficient, it’s a deliberate design decision.

Can You Speed It Up?

Yes — but every shortcut comes with trade-offs.

Some practical options:

Use tools like rimraf for faster deletion
Enable long path support in Windows
Use WSL (Windows Subsystem for Linux) for development workflows
Avoid reinstalling dependencies unnecessarily

You can also disable real-time antivirus scanning temporarily, but that introduces risk and isn’t advisable for regular use.

Final Thoughts

Deleting node_modules slowly on Windows isn’t a bug — it’s the result of intentional system design.

Windows favors safety and control.

Unix-based systems favor speed and simplicity.

Once you understand that trade-off, the behavior makes a lot more sense — even if it’s still frustrating.

DEV Community: Prashant Patil