Full Guide to understand Queries Execution In MySQL Dr Benabderrezak
Introduction
If you want to master relational databases, it's not enough to just know how to write SQL like SELECT, JOIN, or
GROUP BY.
You also need to understand how the database runs these queries behind the scenes.
When you send a query, the database doesn't just fetch data instantly. It follows 4 important steps :
● Parsing : Checks if your SQL is correct.
● Planning : Thinks of different ways to get the result.
● Optimization : Chooses the fastest method.
● Execution : Runs the plan step by step.
Why is this important?
when your tables get big (millions of rows), knowing how queries work will help you make them fast and
efficient.
Query Execution Steps ( Pipeline )
1. Parsing
- The database reads your SQL and checks for mistakes.
- Then it builds an internal "map" of the query (called a parse tree).
2. Planning
- The database looks at different ways to run your query.
- These options are called query plans.
3. Optimization
- It uses data statistics (like number of rows, index info, etc.) to guess which plan is faster.
- Then it picks the best one.
1
Full Guide to understand Queries Execution In MySQL Dr Benabderrezak
4. Execution
The query is run in steps :
● Scan the table or index
● Filter unwanted rows
● Join data from other tables (if needed)
● Sort or group results
● Return the final result
Example 1 : Full Table Scan
SELECT * FROM users WHERE age = 25;
● ❌ If there's no index on age, the database will check every row in the table.
● ✅ Works fine if the table is small.
● 🛑 Slow if there are millions of rows.
Example 2 : Index Scan
CREATE INDEX idx_users_age ON users(age);
SELECT * FROM users WHERE age = 25;
● ✅ Now the database can use the index to find only the matching rows.
● Much faster than scanning the whole table.
Example 3 : Join with Two Tables
SELECT * FROM orders
JOIN customers ON orders.customer_id = customers.id
WHERE customers.country = 'Algeria';
2
Full Guide to understand Queries Execution In MySQL Dr Benabderrezak
The optimizer asks :
● Which table should I read first?
● Should I use a special join method?
● Are there indexes to speed this up?
If you have :
CREATE INDEX idx_country ON customers(country);
Then it can :
● Find Algerian customers using the index.
● Get their IDs
● Look up matching orders faster.
Join Types
✅ Nested Loop Join
● Used when : Tables are small, or there’s an index.
● How it works :
For each row in the first table, find matching rows in the second.
✅ Hash Join
● Used when : Tables are large and have no index.
● How it works :
Build a small hash table in memory from one table.
Then go row by row in the other table and check for matches.
✅ Merge Join
● Used when : Both tables are sorted on the join column.
● How it works :
Walk through both tables like in merge sort, and combine matching rows.
3
Full Guide to understand Queries Execution In MySQL Dr Benabderrezak
Example 4 : Grouping and Aggregation
SELECT country, COUNT(*) FROM users GROUP BY country;
● The database groups rows by country.
● Then it counts how many users are in each group.
● It may use sorting or in-memory hashing to do this.
See How the Query is Run (Query Plan)
Use EXPLAIN in MySQL to see the plan :
EXPLAIN SELECT * FROM users WHERE age = 25;
It will show :
● type : How it reads the table (full scan, index, etc.)
● key : Which index is used (if any)
● rows : How many rows it expects to check
● Extra : Details like "Using where" or "Using index"
Example 5 : Don’t Use Functions on Indexed Columns
SELECT * FROM users WHERE YEAR(birthdate) = 1990;
● Even if birthdate has an index, this query won’t use it.
● Functions like YEAR() make the index useless.
✅ Better version :
SELECT * FROM users
WHERE birthdate BETWEEN '1990-01-01' AND '1990-12-31';
● Now the index is used, and the query is fast.
