Nisha runs the office of a small JEE and NEET coaching centre in Dhanbad, and she has just learned enough SQL to replace the paper fee register. Her first report joins the students table to the payments table and adds up what each student has paid. It looks perfect until the owner counts heads: the centre has six students, and the report lists four. Kunal, who has not paid anything yet, is simply missing, which is exactly the student the owner wanted to call. Then she adds test results to the same query so the report can show marks too, and Aarav's fees jump from ₹45,000 to ₹90,000 even though nobody paid a rupee more. The SQL ran without a single error both times. So why did one join hide students and another one double the money?
The answers are a wrong join type and a problem called fan-out, and both are covered below along with the rest of what you need to write real reports in MySQL. Part 1 covers joins (INNER, LEFT, RIGHT, a FULL OUTER JOIN workaround, self joins and multi-table joins). Part 2 covers aggregate functions, GROUP BY and HAVING. Part 3 explains indexes and how to read EXPLAIN, Part 4 gives the basics of query optimisation, and Part 5 covers transactions and ACID. Every query and every result table on this page was run on MySQL 8.4, so the outputs are real. If tables, primary keys and foreign keys are still new, read the previous post, MySQL Data Types, Primary Keys, Foreign Keys & Relationships, first.
The Sample Database
All examples use four small tables for Nisha's centre. A batches table holds each course and its fee. A students table holds each student, their phone number, town and batch; Priya has just enquired and is not in a batch yet, so her batch_id is NULL. The referred_by column points to another student and is used for the self-join example. A payments table holds every fee instalment, and test_results holds marks. Copy this script into MySQL Workbench or the mysql command-line client to follow along.
CREATE TABLE batches (
batch_id INT PRIMARY KEY,
batch_name VARCHAR(50) NOT NULL,
fee DECIMAL(10,2) NOT NULL
);
CREATE TABLE students (
student_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
phone VARCHAR(15),
city VARCHAR(30),
batch_id INT NULL,
referred_by INT NULL,
FOREIGN KEY (batch_id) REFERENCES batches(batch_id),
FOREIGN KEY (referred_by) REFERENCES students(student_id)
);
CREATE TABLE payments (
payment_id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT NOT NULL,
amount DECIMAL(10,2) NOT NULL,
paid_on DATE NOT NULL,
mode VARCHAR(10) NULL,
FOREIGN KEY (student_id) REFERENCES students(student_id)
);
CREATE TABLE test_results (
result_id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT NOT NULL,
test_name VARCHAR(30) NOT NULL,
marks INT NOT NULL,
FOREIGN KEY (student_id) REFERENCES students(student_id)
);
INSERT INTO batches VALUES
(1, 'JEE Morning', 45000),
(2, 'NEET Evening', 42000),
(3, 'Class 10 Foundation', 18000);
INSERT INTO students (student_id, name, phone, city, batch_id, referred_by) VALUES
(1, 'Aarav', '9431000001', 'Dhanbad', 1, NULL),
(2, 'Ishita', '9431000002', 'Dhanbad', 1, 1),
(3, 'Rohan', '9431000003', 'Jharia', 2, NULL),
(4, 'Sana', NULL, 'Dhanbad', 2, 3),
(5, 'Kunal', '9431000005', 'Sindri', 1, 1),
(6, 'Priya', '9431000006', 'Dhanbad', NULL, NULL);
INSERT INTO payments (student_id, amount, paid_on, mode) VALUES
(1, 20000, '2026-10-05', 'UPI'),
(1, 25000, '2026-11-02', 'UPI'),
(2, 45000, '2026-10-07', 'Cash'),
(3, 21000, '2026-10-10', 'UPI'),
(4, 21000, '2026-11-01', NULL);
INSERT INTO test_results (student_id, test_name, marks) VALUES
(1, 'Physics Unit 1', 72),
(1, 'Maths Unit 1', 65),
(2, 'Physics Unit 1', 81),
(2, 'Maths Unit 1', 77),
(3, 'Biology Unit 1', 88);
Notice the edge cases built into this data on purpose. The Class 10 Foundation batch has no students. Kunal and Priya have no payments. Sana has no phone number and her payment mode was not recorded. Aarav paid in two instalments and took two tests. Real data always has gaps like these, and most reporting bugs come from forgetting them.
Part 1: Joins
What a Join Is, and Why Data Lives in Separate Tables
Many beginners learn SELECT, INSERT, UPDATE and DELETE and feel ready. Then the first real request arrives: "show each payment with the student's name and batch". The amount is in payments, the name is in students and the batch name is in batches. No single table has the answer.
The data is split on purpose. If every payment row also stored the student's name, phone and batch name, then changing Sana's phone number would mean updating every one of her payment rows, and missing one would leave the database contradicting itself. Instead each fact is stored once, and rows point to each other through keys: payments.student_id points to students.student_id. A join follows those pointers at query time and combines matching rows from two tables into one result. Think of a school office with one register of students and another register of batches; the batch number written next to each student lets you look up the batch name whenever you need it.
INNER JOIN: Only Rows That Match
An INNER JOIN returns a row only when the ON condition finds a match in both tables. For every student, MySQL looks for the batch whose batch_id equals the student's batch_id. If it finds one, the two rows are combined; if not, the student is left out.
SELECT s.name, b.batch_name
FROM students s
INNER JOIN batches b ON b.batch_id = s.batch_id
ORDER BY s.student_id;
+--------+--------------+
| name | batch_name |
+--------+--------------+
| Aarav | JEE Morning |
| Ishita | JEE Morning |
| Rohan | NEET Evening |
| Sana | NEET Evening |
| Kunal | JEE Morning |
+--------+--------------+
5 rows
Priya is missing because her batch_id is NULL, and NULL never equals anything, not even another NULL. The Class 10 batch is missing because no student points to it. The s and b after the table names are aliases, short names that keep the query readable; JOIN on its own means the same as INNER JOIN.
The same pattern produces a payment register, which is the kind of "orders with customer details" report every business needs:
SELECT p.payment_id, s.name, p.amount, p.paid_on
FROM payments p
INNER JOIN students s ON s.student_id = p.student_id
ORDER BY p.payment_id;
+------------+--------+----------+------------+
| payment_id | name | amount | paid_on |
+------------+--------+----------+------------+
| 1 | Aarav | 20000.00 | 2026-10-05 |
| 2 | Aarav | 25000.00 | 2026-11-02 |
| 3 | Ishita | 45000.00 | 2026-10-07 |
| 4 | Rohan | 21000.00 | 2026-10-10 |
| 5 | Sana | 21000.00 | 2026-11-01 |
+------------+--------+----------+------------+
Here INNER JOIN is exactly right: every payment belongs to a student (the foreign key guarantees it), so nothing can be lost. Now look at Nisha's original fee report, which starts from students instead:
SELECT s.name, SUM(p.amount) AS total_paid
FROM students s
INNER JOIN payments p ON p.student_id = s.student_id
GROUP BY s.student_id, s.name
ORDER BY s.student_id;
+--------+------------+
| name | total_paid |
+--------+------------+
| Aarav | 45000.00 |
| Ishita | 45000.00 |
| Rohan | 21000.00 |
| Sana | 21000.00 |
+--------+------------+
4 rows -- but SELECT COUNT(*) FROM students returns 6
The question was "what has each student paid?", and a student who paid nothing should appear with ₹0. INNER JOIN cannot do that, because Kunal and Priya have no matching payment row. Every time you write a join, ask one question: may the row on the other side be missing, and if so, should my row still appear?
LEFT JOIN: Keep Every Row From the Left Table
A LEFT JOIN (also written LEFT OUTER JOIN) returns every row from the table written first, the left table. Where a match exists on the right, the columns are filled in. Where none exists, the row still appears and the right-hand columns are NULL.
SELECT s.name, p.amount, p.paid_on
FROM students s
LEFT JOIN payments p ON p.student_id = s.student_id
ORDER BY s.student_id, p.paid_on;
+--------+----------+------------+
| name | amount | paid_on |
+--------+----------+------------+
| Aarav | 20000.00 | 2026-10-05 |
| Aarav | 25000.00 | 2026-11-02 |
| Ishita | 45000.00 | 2026-10-07 |
| Rohan | 21000.00 | 2026-10-10 |
| Sana | 21000.00 | 2026-11-01 |
| Kunal | NULL | NULL |
| Priya | NULL | NULL |
+--------+----------+------------+
Nisha's fixed report uses LEFT JOIN, and wraps the sum in COALESCE(..., 0), which returns the first non-NULL value. Without it, Kunal's total would show NULL, because SUM of nothing is NULL, not zero.
SELECT s.name, COALESCE(SUM(p.amount), 0) AS total_paid
FROM students s
LEFT JOIN payments p ON p.student_id = s.student_id
GROUP BY s.student_id, s.name
ORDER BY s.student_id;
+--------+------------+
| name | total_paid |
+--------+------------+
| Aarav | 45000.00 |
| Ishita | 45000.00 |
| Rohan | 21000.00 |
| Sana | 21000.00 |
| Kunal | 0.00 |
| Priya | 0.00 |
+--------+------------+
LEFT JOIN also answers "who has not?" questions: students who have never paid, sellers who have not listed a product, customers who never ordered. Join, then keep only the rows where the right side found nothing. Test a column that can never be NULL in a real match, such as the right table's primary key.
-- Students with no payment at all: the owner's call list
SELECT s.name, s.phone
FROM students s
LEFT JOIN payments p ON p.student_id = s.student_id
WHERE p.payment_id IS NULL;
+-------+------------+
| name | phone |
+-------+------------+
| Kunal | 9431000005 |
| Priya | 9431000006 |
+-------+------------+
The WHERE Trap That Turns a LEFT JOIN Into an INNER JOIN
Suppose the owner wants every student with their November payments, if any. The obvious query puts the date filter in WHERE:
SELECT s.name, p.amount, p.paid_on
FROM students s
LEFT JOIN payments p ON p.student_id = s.student_id
WHERE p.paid_on >= '2026-11-01'
ORDER BY s.student_id;
+-------+----------+------------+
| name | amount | paid_on |
+-------+----------+------------+
| Aarav | 25000.00 | 2026-11-02 |
| Sana | 21000.00 | 2026-11-01 |
+-------+----------+------------+
Four students vanished. The LEFT JOIN did produce Kunal's row with paid_on = NULL, but then WHERE ran and NULL >= '2026-11-01' is not true, so the row was thrown away. A condition on the right-hand table belongs in the ON clause, where it limits which rows count as a match instead of filtering the final result:
SELECT s.name, p.amount, p.paid_on
FROM students s
LEFT JOIN payments p ON p.student_id = s.student_id
AND p.paid_on >= '2026-11-01'
ORDER BY s.student_id;
+--------+----------+------------+
| name | amount | paid_on |
+--------+----------+------------+
| Aarav | 25000.00 | 2026-11-02 |
| Ishita | NULL | NULL |
| Rohan | NULL | NULL |
| Sana | 21000.00 | 2026-11-01 |
| Kunal | NULL | NULL |
| Priya | NULL | NULL |
+--------+----------+------------+
The rule of thumb: conditions on the left table go in WHERE; conditions on the right table of a LEFT JOIN go in ON, unless you deliberately want only matched rows (as in the IS NULL check above).
RIGHT JOIN: The Mirror Image
A RIGHT JOIN keeps every row from the table written second. This query lists every batch, including the empty Class 10 batch:
SELECT s.name, b.batch_name
FROM students s
RIGHT JOIN batches b ON b.batch_id = s.batch_id
ORDER BY b.batch_id, s.student_id;
+--------+---------------------+
| name | batch_name |
+--------+---------------------+
| Aarav | JEE Morning |
| Ishita | JEE Morning |
| Kunal | JEE Morning |
| Rohan | NEET Evening |
| Sana | NEET Evening |
| NULL | Class 10 Foundation |
+--------+---------------------+
Any RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the two tables (FROM batches b LEFT JOIN students s ... gives the same rows), and most teams do exactly that. Reading top to bottom, "start from batches, attach students if any" is easier to follow than "attach students, but actually keep all batches". RIGHT JOIN is still worth knowing because you will meet it in other people's queries, and it is needed for the FULL OUTER JOIN workaround below.
FULL OUTER JOIN: Not in MySQL, but Easy to Build
A full outer join returns every row from both tables: matched pairs, left rows with no match and right rows with no match. PostgreSQL, SQL Server and Oracle support it directly. MySQL does not, and the error is not very helpful:
SELECT s.name, b.batch_name
FROM students s
FULL OUTER JOIN batches b ON b.batch_id = s.batch_id;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'FULL OUTER JOIN batches b ON b.batch_id = s.batch_id' at line 3
The standard workaround combines a LEFT JOIN and a RIGHT JOIN with UNION, which stacks two result sets and removes duplicate rows. The matched rows appear in both halves, and UNION keeps one copy:
SELECT s.name, b.batch_name
FROM students s
LEFT JOIN batches b ON b.batch_id = s.batch_id
UNION
SELECT s.name, b.batch_name
FROM students s
RIGHT JOIN batches b ON b.batch_id = s.batch_id;
+--------+---------------------+
| name | batch_name |
+--------+---------------------+
| Aarav | JEE Morning |
| Ishita | JEE Morning |
| Rohan | NEET Evening |
| Sana | NEET Evening |
| Kunal | JEE Morning |
| Priya | NULL | <- student without a batch (from the LEFT half)
| NULL | Class 10 Foundation | <- batch without students (from the RIGHT half)
+--------+---------------------+
There is a catch. UNION removes all identical rows, including genuine duplicates, so two different students both called "Rohan" in the same batch would collapse into one line here. The safer version keeps every row of the LEFT half with UNION ALL and takes only the unmatched rows from the RIGHT half. It gives the same seven rows on this data, and it stays correct when names repeat. It also skips the duplicate-removal step, which is extra work on large results.
SELECT s.name, b.batch_name
FROM students s
LEFT JOIN batches b ON b.batch_id = s.batch_id
UNION ALL
SELECT s.name, b.batch_name
FROM students s
RIGHT JOIN batches b ON b.batch_id = s.batch_id
WHERE s.student_id IS NULL; -- only batches that matched no student
The Four Joins as a Picture
Draw each table as a circle, with the overlap standing for rows that match. The shaded part shows which rows each join keeps.
Treat this picture as a memory aid, not a precise model. Venn diagrams describe sets of distinct items, but a join can multiply rows: Aarav appears twice in the LEFT JOIN result above because he has two payments. That multiplication is the source of Nisha's second bug, covered in a moment.
Self Join: A Table Joined to Itself
Sometimes the relationship is inside one table. Each student's referred_by holds the ID of another student, so to print "who referred whom" you join students to itself under two different aliases. The same pattern handles employees and their managers, or categories and parent categories.
SELECT s.name AS student, r.name AS referred_by
FROM students s
INNER JOIN students r ON r.student_id = s.referred_by
ORDER BY s.student_id;
+---------+-------------+
| student | referred_by |
+---------+-------------+
| Ishita | Aarav |
| Sana | Rohan |
| Kunal | Aarav |
+---------+-------------+
Joining More Than Two Tables
Real reports often chain several joins. Each JOIN ... ON attaches one more table to the rows built so far. Here a payment leads to its student, and the student leads to their batch:
SELECT p.payment_id, s.name, b.batch_name, p.amount, p.mode
FROM payments p
INNER JOIN students s ON s.student_id = p.student_id
INNER JOIN batches b ON b.batch_id = s.batch_id
ORDER BY p.payment_id;
+------------+--------+--------------+----------+------+
| payment_id | name | batch_name | amount | mode |
+------------+--------+--------------+----------+------+
| 1 | Aarav | JEE Morning | 20000.00 | UPI |
| 2 | Aarav | JEE Morning | 25000.00 | UPI |
| 3 | Ishita | JEE Morning | 45000.00 | Cash |
| 4 | Rohan | NEET Evening | 21000.00 | UPI |
| 5 | Sana | NEET Evening | 21000.00 | NULL |
+------------+--------+--------------+----------+------+
You can mix join types in one query. The dues report below needs every student who is in a batch (INNER JOIN to batches, because a fee only exists for a batch) and their payments if any (LEFT JOIN to payments). Once a LEFT JOIN appears, think carefully before adding an INNER JOIN after it on a column from the optional side, because that would drop the unmatched rows again.
SELECT s.name, b.batch_name, b.fee,
COALESCE(SUM(p.amount), 0) AS paid,
b.fee - COALESCE(SUM(p.amount), 0) AS due
FROM students s
INNER JOIN batches b ON b.batch_id = s.batch_id
LEFT JOIN payments p ON p.student_id = s.student_id
GROUP BY s.student_id, s.name, b.batch_name, b.fee
ORDER BY due DESC, s.name;
+--------+--------------+----------+----------+----------+
| name | batch_name | fee | paid | due |
+--------+--------------+----------+----------+----------+
| Kunal | JEE Morning | 45000.00 | 0.00 | 45000.00 |
| Rohan | NEET Evening | 42000.00 | 21000.00 | 21000.00 |
| Sana | NEET Evening | 42000.00 | 21000.00 | 21000.00 |
| Aarav | JEE Morning | 45000.00 | 45000.00 | 0.00 |
| Ishita | JEE Morning | 45000.00 | 45000.00 | 0.00 |
+--------+--------------+----------+----------+----------+
The Fan-Out Problem: Why Nisha's Fees Doubled
test_results and a COUNT. The query runs, but Aarav's fees now read ₹90,000 and Ishita's too, while the receipt book clearly shows ₹45,000 each.SELECT s.name,
SUM(p.amount) AS total_paid,
COUNT(t.result_id) AS tests_taken
FROM students s
LEFT JOIN payments p ON p.student_id = s.student_id
LEFT JOIN test_results t ON t.student_id = s.student_id
GROUP BY s.student_id, s.name
ORDER BY s.student_id;
+--------+------------+-------------+
| name | total_paid | tests_taken |
+--------+------------+-------------+
| Aarav | 90000.00 | 4 | <- wrong: paid 45000, took 2 tests
| Ishita | 90000.00 | 2 | <- wrong: paid 45000
| Rohan | 21000.00 | 1 |
| Sana | 21000.00 | 0 |
| Kunal | NULL | 0 |
| Priya | NULL | 0 |
+--------+------------+-------------+
To see why, remove the GROUP BY and look at Aarav's raw rows. He has 2 payments and 2 test results, and the join pairs every payment with every test result, giving 2 × 2 = 4 rows. Each payment is now counted twice, and each test is counted twice.
SELECT s.name, p.amount, t.test_name
FROM students s
JOIN payments p ON p.student_id = s.student_id
JOIN test_results t ON t.student_id = s.student_id
WHERE s.student_id = 1;
+-------+----------+----------------+
| name | amount | test_name |
+-------+----------+----------------+
| Aarav | 20000.00 | Physics Unit 1 |
| Aarav | 20000.00 | Maths Unit 1 |
| Aarav | 25000.00 | Physics Unit 1 |
| Aarav | 25000.00 | Maths Unit 1 |
+-------+----------+----------------+
This is fan-out: joining one parent row to two independent "many" tables multiplies their rows together. It never causes an error, which is what makes it dangerous; the totals are simply wrong, and they look reasonable enough to reach a WhatsApp group or a fee reminder. Rohan's numbers were right only because he has one payment and one test, so 1 × 1 = 1.
The fix is to aggregate each "many" table separately, so each becomes one row per student, and only then join. Each inner query is a derived table, a subquery in the FROM clause that acts like a temporary table:
SELECT s.name,
COALESCE(pay.total_paid, 0) AS total_paid,
COALESCE(tr.tests_taken, 0) AS tests_taken
FROM students s
LEFT JOIN (SELECT student_id, SUM(amount) AS total_paid
FROM payments GROUP BY student_id) pay
ON pay.student_id = s.student_id
LEFT JOIN (SELECT student_id, COUNT(*) AS tests_taken
FROM test_results GROUP BY student_id) tr
ON tr.student_id = s.student_id
ORDER BY s.student_id;
+--------+------------+-------------+
| name | total_paid | tests_taken |
+--------+------------+-------------+
| Aarav | 45000.00 | 2 |
| Ishita | 45000.00 | 2 |
| Rohan | 21000.00 | 1 |
| Sana | 21000.00 | 0 |
| Kunal | 0.00 | 0 |
| Priya | 0.00 | 0 |
+--------+------------+-------------+
A correlated subquery in the SELECT list also works, and some people find it easier to read: (SELECT COALESCE(SUM(p.amount), 0) FROM payments p WHERE p.student_id = s.student_id) AS total_paid returns the same six correct totals. It runs once per student, so for big reports the derived-table version is usually the better default. COUNT(DISTINCT t.result_id) would fix the test count in the broken query, but there is no equally safe trick for SUM, because two different payments of the same amount are not duplicates. A quick sanity check catches fan-out early: compare the grand total of your report with a plain SELECT SUM(amount) FROM payments.
Everyday Reports and the Join Each One Needs
Most business reports are one of a few shapes. The trick is to decide the join type from the question, not from habit.
| Report | Tables | Join | Why |
|---|---|---|---|
| Batch roster: student name and batch | students, batches | INNER | Only students who are in a batch belong on a roster |
| Payment register with student names | payments, students | INNER | Every payment has a student (foreign key) |
| Fee status of every student, including ₹0 | students, payments | LEFT | Students without payments must still appear |
| Students who have never paid | students, payments | LEFT + IS NULL | "Who has not" questions are anti-joins |
| Batches with student counts, including empty ones | batches, students | LEFT from batches | Empty batches must show 0 |
| Payments with student and batch | payments, students, batches | Two joins | Follow the keys one table at a time |
The same shapes appear in any business. In an online store, "orders with customer names" is an INNER JOIN, "sellers who have not listed a product yet" is a LEFT JOIN with IS NULL, and "orders with their delivery status, including orders not yet shipped" is a LEFT JOIN from orders to deliveries.
What Happens If You Forget the ON Condition
You might expect an error. In MySQL there isn't one: INNER JOIN without ON is treated as a cross join, which pairs every row of one table with every row of the other.
SELECT COUNT(*) AS row_count
FROM students s
INNER JOIN batches b; -- no ON clause
+-----------+
| row_count |
+-----------+
| 18 | -- 6 students x 3 batches, mostly nonsense pairs
+-----------+
With 6 and 3 rows that is a harmless 18. With 50,000 students and 50,000 payments it is 2.5 billion rows, enough to stall the server. Always write the ON clause, and join on the key columns that the foreign key defines (usually a primary key on one side and a foreign key on the other), not on columns that merely have similar names.
Part 2: Aggregate Functions, GROUP BY and HAVING
Once the data is joined, the owner rarely wants every row. They want summaries: how many students, how much collected this month, the average instalment, the largest payment. Aggregate functions take many rows and return one value. The five you will use constantly are COUNT, SUM, AVG, MIN and MAX, and almost every number on a dashboard comes from one of them.
COUNT(*) vs COUNT(column) vs COUNT(DISTINCT)
These three look similar and answer different questions. COUNT(*) counts rows. COUNT(column) counts rows where that column is not NULL. COUNT(DISTINCT column) counts different non-NULL values.
SELECT COUNT(*) AS all_rows,
COUNT(phone) AS with_phone,
COUNT(batch_id) AS in_a_batch,
COUNT(DISTINCT city) AS cities
FROM students;
+----------+------------+------------+--------+
| all_rows | with_phone | in_a_batch | cities |
+----------+------------+------------+--------+
| 6 | 5 | 5 | 3 |
+----------+------------+------------+--------+
SELECT COUNT(*) AS payments, COUNT(mode) AS mode_recorded FROM payments;
+----------+---------------+
| payments | mode_recorded |
+----------+---------------+
| 5 | 4 |
+----------+---------------+
Sana has no phone, so COUNT(phone) is 5; Priya has no batch, so COUNT(batch_id) is 5. Neither is wrong, but they answer "how many students have a phone number?" rather than "how many students are there?". Use COUNT(*) to count rows, and COUNT(column) only when you mean "rows where this is filled in". With a LEFT JOIN, COUNT(right_table.id) is exactly what gives 0 for unmatched rows, as in the batch counts later.
SUM, AVG, MIN and MAX
SELECT SUM(amount) AS collected,
AVG(amount) AS avg_payment,
MIN(amount) AS smallest,
MAX(amount) AS largest
FROM payments;
+-----------+--------------+----------+----------+
| collected | avg_payment | smallest | largest |
+-----------+--------------+----------+----------+
| 132000.00 | 26400.000000 | 20000.00 | 45000.00 |
+-----------+--------------+----------+----------+
Two NULL rules catch people out. First, all of these functions ignore NULL values, which matters most for AVG: the average of 100, 200 and NULL is 150 (two values), not 100. If a missing value should count as zero, say so with COALESCE. Second, when no rows match, SUM, AVG, MIN and MAX return NULL, while COUNT returns 0.
SELECT AVG(x) AS avg_ignoring_nulls, AVG(COALESCE(x, 0)) AS avg_nulls_as_zero
FROM (SELECT 100 AS x UNION ALL SELECT 200 UNION ALL SELECT NULL) t;
+--------------------+-------------------+
| avg_ignoring_nulls | avg_nulls_as_zero |
+--------------------+-------------------+
| 150.0000 | 100.0000 |
+--------------------+-------------------+
SELECT SUM(amount) AS collected FROM payments WHERE paid_on = '2026-12-25';
-- collected: NULL (no payments that day)
SELECT COALESCE(SUM(amount), 0) AS collected FROM payments WHERE paid_on = '2026-12-25';
-- collected: 0.00 (what a dashboard should show)
Remember the difference between COUNT and SUM too: COUNT(amount) says 5 payments were made, SUM(amount) says ₹1,32,000 came in. Store money in DECIMAL, as here, never in FLOAT or DOUBLE, so totals come out exact to the paisa.
Filter First With WHERE, Using Date Ranges
WHERE runs before aggregation, so it decides which rows are summarised. October's collection in one query:
SELECT COUNT(*) AS payments,
SUM(amount) AS collected,
AVG(amount) AS average_payment,
MIN(amount) AS smallest,
MAX(amount) AS largest
FROM payments
WHERE paid_on >= '2026-10-01' AND paid_on < '2026-11-01';
+----------+-----------+-----------------+----------+----------+
| payments | collected | average_payment | smallest | largest |
+----------+-----------+-----------------+----------+----------+
| 3 | 86000.00 | 28666.666667 | 20000.00 | 45000.00 |
+----------+-----------+-----------------+----------+----------+
A common way to write "this month" is WHERE MONTH(paid_on) = MONTH(CURDATE()). It has two problems. It matches November of every year, and it wraps the column in a function, which stops MySQL from using an index on it (shown in Part 3). With one extra payment from November 2025 added to the table, the two versions disagree:
-- after adding a ₹15,000 payment dated 2025-11-20
SELECT COUNT(*) AS payments, SUM(amount) AS collected
FROM payments WHERE MONTH(paid_on) = 11;
-- payments: 3, collected: 61000.00 (includes last year!)
SELECT COUNT(*) AS payments, SUM(amount) AS collected
FROM payments WHERE paid_on >= '2026-11-01' AND paid_on < '2026-12-01';
-- payments: 2, collected: 46000.00 (correct)
Use a half-open range, >= first day and < first day of next period. It is correct for every year, works the same for DATETIME columns (no worrying about 23:59:59), and can use an index. For "today", use paid_on = CURDATE() on a DATE column, or >= CURDATE() AND < CURDATE() + INTERVAL 1 DAY on a DATETIME.
GROUP BY: One Summary Row per Group
GROUP BY splits the rows into groups and runs the aggregates once per group. Collection by payment mode:
SELECT mode, COUNT(*) AS payments, SUM(amount) AS collected
FROM payments
GROUP BY mode
ORDER BY collected DESC;
+------+----------+-----------+
| mode | payments | collected |
+------+----------+-----------+
| UPI | 3 | 66000.00 |
| Cash | 1 | 45000.00 |
| NULL | 1 | 21000.00 |
+------+----------+-----------+
NULL forms its own group, which is useful here: it shows that one payment needs its mode filled in. Every column in the SELECT list must either be inside an aggregate or be part of the group. MySQL 8 enforces this by default through the ONLY_FULL_GROUP_BY SQL mode, so a "vendor name with product count" style query without GROUP BY fails:
SELECT b.batch_name, COUNT(s.student_id) AS students
FROM batches b
INNER JOIN students s ON s.batch_id = b.batch_id;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT
list contains nonaggregated column 'dbdata.b.batch_name'; this is incompatible with
sql_mode=only_full_group_by
SELECT b.batch_name, COUNT(s.student_id) AS students
FROM batches b
INNER JOIN students s ON s.batch_id = b.batch_id
GROUP BY b.batch_id, b.batch_name;
+--------------+----------+
| batch_name | students |
+--------------+----------+
| JEE Morning | 3 |
| NEET Evening | 2 |
+--------------+----------+
(dbdata is just the name of the test database.) Older MySQL versions accepted the first query and returned an arbitrary batch name next to the overall count, a silent wrong answer. Do not "fix" the error by turning the mode off; add the GROUP BY. Grouping by the primary key as well as the name is a good habit, because two batches or two students can share a name.
HAVING vs WHERE
WHERE filters individual rows before grouping. HAVING filters groups after the aggregates are calculated. "Students whose UPI payments total more than ₹30,000" needs both: WHERE keeps only UPI rows, HAVING keeps only the big totals.
SELECT s.name, SUM(p.amount) AS total_paid
FROM students s
JOIN payments p ON p.student_id = s.student_id
WHERE p.mode = 'UPI'
GROUP BY s.student_id, s.name
HAVING SUM(p.amount) > 30000;
+-------+------------+
| name | total_paid |
+-------+------------+
| Aarav | 45000.00 |
+-------+------------+
-- An aggregate in WHERE is not allowed, because the groups don't exist yet:
SELECT s.name, SUM(p.amount) AS total_paid
FROM students s JOIN payments p ON p.student_id = s.student_id
WHERE SUM(p.amount) > 30000
GROUP BY s.student_id, s.name;
ERROR 1111 (HY000): Invalid use of group function
MySQL evaluates the clauses in roughly this order: FROM and joins, WHERE, GROUP BY, aggregates, HAVING, SELECT, ORDER BY, LIMIT. Put a condition in WHERE whenever it does not depend on an aggregate, because filtering rows early means fewer rows to group.
Aggregates With Joins
Joins and aggregates together produce the reports owners actually ask for. Students and collection per batch, including the empty batch:
SELECT b.batch_name,
COUNT(DISTINCT s.student_id) AS students,
COALESCE(SUM(p.amount), 0) AS collected
FROM batches b
LEFT JOIN students s ON s.batch_id = b.batch_id
LEFT JOIN payments p ON p.student_id = s.student_id
GROUP BY b.batch_id, b.batch_name
ORDER BY b.batch_id;
+---------------------+----------+-----------+
| batch_name | students | collected |
+---------------------+----------+-----------+
| JEE Morning | 3 | 90000.00 |
| NEET Evening | 2 | 42000.00 |
| Class 10 Foundation | 0 | 0.00 |
+---------------------+----------+-----------+
This one is not fan-out, even though it has two joins. The joins form a chain (batch → student → payment), so each payment row appears exactly once. COUNT(DISTINCT s.student_id) is needed only because a student with two payments appears on two rows. Fan-out happens when one row has two independent one-to-many branches, like payments and test results hanging off the same student.
To average a per-student figure, aggregate twice. A query such as SELECT AVG(total_sales) FROM vendors only works if someone already stored the totals; with raw payments you compute each student's total in a derived table and then average those:
SELECT AVG(total_paid) AS avg_paid_per_paying_student
FROM (SELECT student_id, SUM(amount) AS total_paid
FROM payments GROUP BY student_id) t;
+-----------------------------+
| avg_paid_per_paying_student |
+-----------------------------+
| 33000.000000 |
+-----------------------------+
Dashboards Are Mostly Aggregates
Each tile on an admin dashboard is usually one aggregate query. The table maps common tiles to functions, and the query after it fetches several tiles in one round trip using scalar subqueries (subqueries that return a single value).
| Dashboard tile | Function | Typical filter |
|---|---|---|
| Total students, payments today | COUNT(*) | paid_on = CURDATE() |
| Collection this month | SUM(amount) | Half-open date range |
| Average instalment or order value | AVG(amount) | Same range, so it compares fairly |
| Lowest stock, smallest payment | MIN() | Active items only |
| Largest payment, highest marks | MAX() | Per batch with GROUP BY |
SELECT
(SELECT COUNT(*) FROM students) AS total_students,
(SELECT COUNT(*) FROM batches) AS total_batches,
(SELECT COALESCE(SUM(amount), 0) FROM payments
WHERE paid_on >= '2026-11-01' AND paid_on < '2026-12-01') AS november_collection,
(SELECT MAX(amount) FROM payments) AS largest_payment;
+----------------+---------------+---------------------+-----------------+
| total_students | total_batches | november_collection | largest_payment |
+----------------+---------------+---------------------+-----------------+
| 6 | 3 | 46000.00 | 45000.00 |
+----------------+---------------+---------------------+-----------------+
Let SQL do these calculations rather than fetching every row into Node.js or Excel and adding them up there. The database sends back one row instead of thousands, and the logic lives in one place.
Subqueries in WHERE, and the NOT IN Trap
A subquery can also supply a value to compare against. Payments larger than the average payment:
SELECT s.name, p.amount
FROM payments p
JOIN students s ON s.student_id = p.student_id
WHERE p.amount > (SELECT AVG(amount) FROM payments);
+--------+----------+
| name | amount |
+--------+----------+
| Ishita | 45000.00 |
+--------+----------+
Be careful with NOT IN when the subquery can return NULL. "Students who have not referred anyone" looks simple, yet returns nothing, because referred_by contains NULLs and x NOT IN (1, 3, NULL) is never true. NOT EXISTS (or the LEFT JOIN with IS NULL from Part 1) gives the right answer:
SELECT name FROM students
WHERE student_id NOT IN (SELECT referred_by FROM students);
-- Empty set (wrong)
SELECT name FROM students s
WHERE NOT EXISTS (SELECT 1 FROM students r WHERE r.referred_by = s.student_id);
-- Ishita, Sana, Kunal, Priya (correct)
Part 3: Indexes
SELECT ... WHERE phone = '...'. It still feels instant, but Nisha has read that "it works on my data" is not the same as "it scales", so she puts EXPLAIN in front of the query. The answer is type = ALL and rows = 5006: MySQL is reading every student record to find one phone number. With three times the records it would read three times as many rows for the same single answer.The Library Catalogue Idea
Imagine a library with lakhs of books and no catalogue. To find one title, the librarian walks every shelf. With a catalogue sorted by title, the librarian looks up the title, reads "Shelf B12, row 4", and walks straight there. A database index is that catalogue: a separate, sorted structure that stores the values of one or more columns together with a pointer to the full row. Without one, MySQL has to do a full table scan, reading every row and checking it. The phone book in your mobile works the same way; it is sorted by name, so finding "Rohan" never means scrolling through every contact.
Seeing the Full Table Scan
To make the plans meaningful, the test database was filled with 5,000 generated students and 8,000 generated payments, using a recursive CTE (a query that produces the numbers 1 to N). Then ANALYZE TABLE refreshes the statistics MySQL uses to plan queries.
SET SESSION cte_max_recursion_depth = 10000;
INSERT INTO students (name, phone, city, batch_id)
WITH RECURSIVE seq (n) AS (
SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < 5000
)
SELECT ELT(1 + n % 10, 'Aditi','Rahul','Meera','Vivek','Tanya',
'Sahil','Neha','Arjun','Pooja','Imran'),
CONCAT('98', LPAD(n, 8, '0')),
ELT(1 + n % 8, 'Dhanbad','Jharia','Sindri','Bokaro',
'Asansol','Giridih','Katras','Nirsa'),
1 + n % 2
FROM seq;
-- Records: 5000 Duplicates: 0 Warnings: 0
INSERT INTO payments (student_id, amount, paid_on, mode)
WITH RECURSIVE seq (n) AS (
SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < 8000
)
SELECT 7 + (n % 5000),
5000 + (n % 9) * 2500,
DATE_ADD('2025-04-01', INTERVAL (n % 580) DAY),
ELT(1 + n % 3, 'UPI', 'Cash', 'Card')
FROM seq;
-- 8000 rows affected
ANALYZE TABLE students, payments;
-- students: 5006 rows, payments: 8005 rows
Now the phone search, and its plan. Put EXPLAIN in front of a SELECT and MySQL shows how it intends to run the query instead of running it. The columns below are trimmed to the important ones; the full output also has id, select_type, key_len, ref and filtered.
SELECT student_id, name, city FROM students WHERE phone = '9800004321';
-- 4327 | Rahul | Jharia
EXPLAIN SELECT student_id, name, city FROM students WHERE phone = '9800004321';
+----------+------+---------------+------+------+-------------+
| table | type | possible_keys | key | rows | Extra |
+----------+------+---------------+------+------+-------------+
| students | ALL | NULL | NULL | 5006 | Using where |
+----------+------+---------------+------+------+-------------+
Read it column by column. type = ALL means a full table scan. possible_keys = NULL means no index could even be considered, and key = NULL means none was chosen. rows = 5006 is MySQL's estimate of how many rows it must examine, and Using where means each of them is tested against the condition.
CREATE INDEX, and the Plan After
CREATE INDEX idx_students_phone ON students (phone);
EXPLAIN SELECT student_id, name, city FROM students WHERE phone = '9800004321';
+----+-------------+----------+------+--------------------+--------------------+---------+-------+------+----------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------+--------------------+--------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | students | ref | idx_students_phone | idx_students_phone | 63 | const | 1 | 100 | NULL |
+----+-------------+----------+------+--------------------+--------------------+---------+-------+------+----------+-------+
Now type = ref (a lookup by an index value), key = idx_students_phone, and rows = 1: MySQL expects to read one row instead of 5,006. ref = const says the value being looked up is a constant from the query. The work is now roughly the same whether the table holds 5,000 rows or 50 lakh, while the full scan grows with every row added. This page does not quote timings, because they depend on the machine, the cache and the data; the rows estimate is the honest, machine-independent way to compare plans. EXPLAIN FORMAT=TREE shows the same story in words, Index lookup on students using idx_students_phone after the index versus Table scan on students before it, and EXPLAIN ANALYZE actually runs the query and reports measured rows and time on your own server.
If the column should never hold duplicates, such as a login email or a roll number, create a UNIQUE INDEX instead. It speeds up lookups in the same way and also makes MySQL reject a duplicate value. Phone numbers are left non-unique here because siblings often share a parent's number.
How a B-Tree Finds a Row, in Simple Words
InnoDB, MySQL's default storage engine, stores indexes as B-trees (strictly, B+trees). The name sounds scary; the idea is a sorted tree of signposts. The top node says which range of values each branch holds, each level narrows the range, and the bottom level (the leaves) holds the sorted values with pointers to rows. To find student 4327 in the sketch below, MySQL reads three nodes instead of 5,006 rows.
Real InnoDB nodes are 16 KB pages holding hundreds of keys each, so even a table with crores of rows needs only a few levels. Because the leaves are sorted and linked, a B-tree also handles ranges (BETWEEN, >, LIKE 'abc%') and ORDER BY on the indexed column, not just exact matches.
Primary Keys, the Clustered Index and Secondary Indexes
You never created an index on student_id, yet a lookup by it is the fastest plan MySQL has:
EXPLAIN SELECT name FROM students WHERE student_id = 42;
+----------+-------+---------------+---------+------+-------+
| table | type | possible_keys | key | rows | Extra |
+----------+-------+---------------+---------+------+-------+
| students | const | PRIMARY | PRIMARY | 1 | NULL |
+----------+-------+---------------+---------+------+-------+
In InnoDB the primary key is the table: rows are stored inside a B-tree ordered by primary key. This is the clustered index, like books physically shelved in catalogue-number order. Every other index is a secondary index (the "non-clustered" index of interview questions), like a separate catalogue card: its leaves store the indexed value plus the row's primary key, and MySQL then looks up the full row in the clustered index. That is one reason to keep primary keys short, such as an INT or BIGINT, because every secondary index carries a copy of it.
InnoDB also creates an index automatically for each foreign key if a suitable one does not already exist. Listing the indexes on students shows batch_id and referred_by, which nobody created by hand:
SELECT INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'students'
ORDER BY INDEX_NAME, SEQ_IN_INDEX;
+------------------------+--------------+-------------+
| INDEX_NAME | SEQ_IN_INDEX | COLUMN_NAME |
+------------------------+--------------+-------------+
| batch_id | 1 | batch_id | <- from the FOREIGN KEY
| idx_students_city_name | 1 | city | <- composite, next section
| idx_students_city_name | 2 | name |
| idx_students_phone | 1 | phone |
| PRIMARY | 1 | student_id |
| referred_by | 1 | referred_by | <- from the FOREIGN KEY
+------------------------+--------------+-------------+
SHOW INDEX FROM students; gives the same list with more detail, including the estimated number of distinct values (Cardinality). Primary key and foreign key indexes are also what make joins fast. In the plan for payments JOIN students, the students side shows type = eq_ref on PRIMARY with rows = 1: for each payment, exactly one student is fetched by primary key.
Composite Indexes and the Leftmost-Prefix Rule
Suppose the front desk often searches by town and name together ("Meera from Sindri"). A composite index covers several columns in a fixed order:
CREATE INDEX idx_students_city_name ON students (city, name);
The index is sorted by city first, and by name within each city, like a phone directory sorted by surname and then first name. That order decides which queries can use it. MySQL can use the index for any leftmost prefix of its columns: (city) alone, or (city, name). It cannot jump into the middle and use name alone, just as a directory sorted by surname cannot help you find everyone whose first name is Meera.
EXPLAIN SELECT student_id, phone FROM students WHERE city = 'Sindri' AND name = 'Meera';
-- type: ref | key: idx_students_city_name | key_len: 325 | ref: const,const | rows: 125
EXPLAIN SELECT student_id, phone FROM students WHERE city = 'Sindri';
-- type: ref | key: idx_students_city_name | key_len: 123 | ref: const | rows: 626
EXPLAIN SELECT student_id, phone FROM students WHERE name = 'Meera';
-- type: ALL | key: NULL | key_len: NULL | ref: NULL | rows: 5006
EXPLAIN SELECT student_id, phone FROM students WHERE name = 'Meera' AND city = 'Sindri';
-- type: ref | key: idx_students_city_name | key_len: 325 | ref: const,const | rows: 125
Three details are worth noticing. key_len shows how much of the index was used: 325 bytes means both columns, 123 means only city. The order of conditions in your WHERE does not matter (the last query uses the full index), only the order of columns in the index does. And the name-only search falls back to a full scan, so if that search is common it needs its own index on name. When choosing the column order, put the columns tested with = first and a range column (such as a date) last, because columns after a range condition cannot narrow the search further. A composite index on (city, name) also makes a separate index on (city) alone redundant.
When MySQL Cannot Use Your Index
An index stores the column's values sorted as they are. If the query asks about something the index does not store directly, MySQL cannot search the tree and falls back to scanning. Four common causes, each tested on the same table with idx_students_phone and an index on payments.paid_on:
1. Implicit type conversion. phone is a VARCHAR. Compare it with a number instead of a string, and MySQL must convert every stored phone to a number before comparing, so the index is useless. The query still returns the right row, which is why this bug survives code review; only EXPLAIN reveals it.
EXPLAIN SELECT student_id, name FROM students WHERE phone = 9800004321; -- no quotes
+----------+------+--------------------+------+------+-------------+
| table | type | possible_keys | key | rows | Extra |
+----------+------+--------------------+------+------+-------------+
| students | ALL | idx_students_phone | NULL | 5006 | Using where |
+----------+------+--------------------+------+------+-------------+
SHOW WARNINGS;
-- Warning 1739: Cannot use ref access on index 'idx_students_phone' due to type or
-- collation conversion on field 'phone'
The reverse is safe: WHERE student_id = '42' on an INT column still gives type = const, because MySQL converts the one constant, not every row. In application code this bug usually appears when a phone number or PIN code arrives as a JavaScript number; pass it as a string, and store such "numbers that are really identifiers" as strings in the first place.
2. A leading wildcard in LIKE. A pattern that starts with fixed characters is a range on the sorted index. A pattern that starts with % could match anywhere, so every value must be checked.
EXPLAIN SELECT student_id, name FROM students WHERE phone LIKE '98000043%';
-- type: range | key: idx_students_phone | rows: 100 | Extra: Using index condition
EXPLAIN SELECT student_id, name FROM students WHERE phone LIKE '%4321';
-- type: ALL | key: NULL | rows: 5006 | Extra: Using where
If "search by last four digits" is a real need, store the last four digits in their own indexed column, or look at MySQL's FULLTEXT indexes for word searches in text.
3. A function wrapped around the column. RIGHT(phone, 4), TRIM(phone), LOWER(email), DATE(created_at) and YEAR(paid_on) all hide the column from the index, because the index stores the raw values, not the function results.
EXPLAIN SELECT student_id, name FROM students WHERE TRIM(phone) = '9800004321';
-- type: ALL | key: NULL | rows: 5006
CREATE INDEX idx_payments_paid_on ON payments (paid_on);
EXPLAIN SELECT payment_id, amount FROM payments
WHERE YEAR(paid_on) = 2026 AND MONTH(paid_on) = 11;
-- type: ALL | key: NULL | rows: 7949
EXPLAIN SELECT payment_id, amount FROM payments
WHERE paid_on >= '2026-11-01' AND paid_on < '2026-12-01';
-- type: range | key: idx_payments_paid_on | rows: 15
The fix is to leave the column bare and move the work to the other side: a date range instead of YEAR(), and clean data on the way in (trim phone numbers when saving) instead of on every search. LOWER(email) is usually unnecessary anyway, because MySQL 8's default collation (utf8mb4_0900_ai_ci) already compares case-insensitively. When you truly need to search on an expression, MySQL 8 supports functional indexes such as CREATE INDEX idx_pay_year ON payments ((YEAR(paid_on)));.
4. The optimiser decides a scan is cheaper. If a condition matches a large share of the table, reading the whole table in order can be cheaper than thousands of index lookups, and MySQL may ignore the index on purpose. That is why an index on a column with only two or three values, such as payment mode or gender, rarely helps by itself. Look at rows in EXPLAIN rather than assuming.
Indexes Are Not Free
Every index is a second copy of some columns, kept sorted. Reads get faster, but writes pay for it. Each INSERT must add an entry to every index on the table, each DELETE must remove them, and an UPDATE must move entries in every index that contains a changed column. Indexes also use disk space and memory in the buffer pool, which is the cache that keeps hot data in RAM.
MySQL does not stop you from wasting that effort. Creating a second index on phone succeeds with only a warning:
CREATE INDEX idx_dup ON students (phone);
-- Query OK, 0 rows affected, 1 warning
SHOW WARNINGS;
-- Warning 1831: Duplicate index 'idx_dup' defined on the table 'dbdata.students'.
-- This is deprecated and will be disallowed in a future release.
So index with a reason. Skip indexes on columns nobody searches, sorts or joins on (a profile_background_color column will never need one), on tiny tables such as ten notification templates where a scan is trivial, and on columns with very few distinct values unless they lead a composite index. Be more careful on tables that take heavy writes, such as a log or an attendance table, where each extra index adds work to every insert. Review the index list now and then and drop ones that duplicate a composite index's prefix.
Which Columns Usually Deserve an Index
| Column | Why | Example |
|---|---|---|
| Primary key | Indexed automatically (clustered index) | student_id, order_id |
| Foreign keys | Used in every join; InnoDB adds one if missing | payments.student_id |
| Login and lookup fields | Searched on every login or support call; often unique | email, phone, roll number |
| Filters on big lists | "My orders", "this month's payments" | (customer_id, created_at) |
| Search and sort columns | Product listing sorted or filtered by them | (category_id, price), product name with prefix search |
A good habit is to look at the actual WHERE, JOIN and ORDER BY clauses your application runs most often, and index for those, rather than guessing in advance.
Part 4: Query Optimisation Basics
Indexes are the roads; the query is the route the driver takes. Two queries can return identical results while doing very different amounts of work, and the cause of a slow page is far more often the query than the server. Before you blame RAM, CPU or the cloud provider, run EXPLAIN. The fuller treatment, including the slow query log, buffer pool sizing and connection pools, is in the MySQL Performance Tuning, Security & Backup post; this part covers the habits every query should have.
Reading EXPLAIN in One Minute
The type column is the quickest signal. From best to worst, the values you have seen on this page are:
| type | Meaning | Seen above |
|---|---|---|
const | At most one row, found by primary key or unique index | WHERE student_id = 42 |
eq_ref | One row per row of the previous table in a join | Joining payments to students by primary key |
ref | Index lookup that may return several rows | WHERE phone = '...' with an index |
range | Index range scan | Date range, LIKE '98000043%' |
index | Reads the whole index in order | ORDER BY paid_on DESC LIMIT 20 |
ALL | Full table scan | No usable index |
Then check key (which index, if any), rows (estimated rows examined) and Extra. In Extra, Using index is good news and Using filesort or Using temporary on a big table deserves a second look.
Select Only the Columns You Need
SELECT * fetches every column: long descriptions, address blocks, audit fields, all sent over the network and held in your application's memory even when the screen shows two of them. It also prevents a useful optimisation. When every column the query needs is inside the index, MySQL answers from the index alone, a covering index, shown as Using index:
EXPLAIN SELECT city, name FROM students WHERE city = 'Sindri';
-- type: ref | key: idx_students_city_name | rows: 626 | Extra: Using index
EXPLAIN SELECT phone FROM students WHERE phone LIKE '98000043%';
-- type: range | key: idx_students_phone | rows: 100 | Extra: Using where; Using index
Ask for phone as well in the first query and the Using index disappears, because MySQL must visit each row to fetch it. Naming columns also protects your code when someone adds a column to the table later.
Filter, Sort and Limit in the Database
Never fetch a whole table and filter it in JavaScript. SELECT * FROM products followed by rows.filter(...) moves every row across the network to keep a handful. Put the condition in WHERE, where an index can help. The same goes for sorting: an index on the sort column lets MySQL read rows already in order and stop after the LIMIT, while sorting on an unindexed column means sorting everything first.
EXPLAIN SELECT payment_id, amount, paid_on FROM payments ORDER BY amount DESC LIMIT 20;
-- type: ALL | key: NULL | rows: 7949 | Extra: Using filesort
EXPLAIN SELECT payment_id, amount, paid_on FROM payments ORDER BY paid_on DESC LIMIT 20;
-- type: index | key: idx_payments_paid_on | rows: 20 | Extra: Backward index scan
Screens show a page at a time, so queries should too. LIMIT 20 returns the first page; LIMIT 20 OFFSET 20 the second, LIMIT 20 OFFSET 40 the third, always with an ORDER BY so the pages are stable. Large offsets get slower, because MySQL still reads and throws away the skipped rows (EXPLAIN for OFFSET 4000 shows rows: 4005). For deep pages or infinite scroll, use keyset pagination: remember the last ID shown and ask for WHERE student_id > 25 ORDER BY student_id LIMIT 5, which jumps straight to the right place in the index.
Fewer Round Trips, Fewer Tables
The N+1 problem is a classic backend mistake: load 100 payments with one query, then run 100 more queries to fetch each student's name, 101 round trips in total. One JOIN returns the same data in a single query. ORMs make this mistake easy to write without noticing, so watch the query log when a list page feels slow. In the other direction, don't join tables you don't use; a "product names" query does not need the vendors, orders and payments tables just because a reporting template had them. And if a dashboard runs the same vendor list query ten times while loading, run it once and reuse the result.
Part 5: Transactions and ACID
Think about a bank transfer of ₹1,000 from Mohan to Farida. The database must take ₹1,000 out of one account and put ₹1,000 into another. If the server crashes or a check fails between those two steps, ₹1,000 has vanished or been created from nothing. The bank cannot accept "half done"; either both steps happen or neither does. The same is true when a coaching centre records a fee and issues a receipt, or a shop creates an order and reduces stock. A transaction groups several statements into one unit of work that either succeeds completely or leaves no trace.
autocommit, START TRANSACTION, COMMIT and ROLLBACK
By default MySQL runs in autocommit mode: every statement is its own transaction and is saved permanently as soon as it succeeds. START TRANSACTION (or BEGIN) switches that off until you finish. COMMIT makes all the changes permanent and visible to others; ROLLBACK undoes everything since START TRANSACTION, as if it never happened.
SELECT @@autocommit, @@transaction_isolation, @@default_storage_engine;
-- 1 | REPEATABLE-READ | InnoDB
CREATE TABLE accounts (
account_id INT PRIMARY KEY,
holder VARCHAR(50) NOT NULL,
balance DECIMAL(12,2) NOT NULL,
CONSTRAINT chk_balance CHECK (balance >= 0)
) ENGINE=InnoDB;
INSERT INTO accounts VALUES (1, 'Mohan', 5000.00), (2, 'Farida', 2000.00);
START TRANSACTION;
UPDATE accounts SET balance = balance - 1000 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE account_id = 2;
COMMIT;
SELECT * FROM accounts;
+------------+--------+---------+
| account_id | holder | balance |
+------------+--------+---------+
| 1 | Mohan | 4000.00 |
| 2 | Farida | 3000.00 |
+------------+--------+---------+
The CHECK constraint (enforced since MySQL 8.0.16) makes the database itself refuse a negative balance, which is a good example of the "C" in ACID that comes up below: UPDATE accounts SET balance = balance - 9000 WHERE account_id = 1; fails with ERROR 3819 (HY000): Check constraint 'chk_balance' is violated.
A Failed Statement Does Not Undo the Transaction
This surprises many developers. When one statement inside a transaction fails, MySQL rolls back that statement only. The transaction stays open, and earlier statements are still pending. If your code then runs COMMIT, the half-finished work is saved. Here the transfer of ₹9,000 credits Farida first, and then the debit fails the check:
START TRANSACTION;
UPDATE accounts SET balance = balance + 9000 WHERE account_id = 2; -- OK, 1 row
UPDATE accounts SET balance = balance - 9000 WHERE account_id = 1;
-- ERROR 3819 (HY000): Check constraint 'chk_balance' is violated.
COMMIT; -- the mistake
SELECT * FROM accounts;
+------------+--------+----------+
| account_id | holder | balance |
+------------+--------+----------+
| 1 | Mohan | 4000.00 |
| 2 | Farida | 12000.00 | <- ₹9,000 created from nothing
+------------+--------+----------+
The total in the bank went from ₹7,000 to ₹16,000. The correct reaction to any error inside a transaction is ROLLBACK, which restores both balances (₹4,000 and ₹3,000) exactly. Application code must therefore catch every error and roll back explicitly, as in the Node.js examples below. (A deadlock, covered later, is the exception: MySQL rolls back the whole transaction for you.)
A related trap: an UPDATE that matches no rows is not an error. UPDATE accounts SET balance = balance + 500 WHERE account_id = 99; succeeds with 0 rows affected, so a transfer to a mistyped account would happily debit the sender. Check the affected-row count after each step.
A Bank Transfer in Node.js
This function uses the mysql2 package. It takes one connection from the pool (a transaction belongs to a single connection, so never run its statements on pool.query, which may pick different connections), makes the debit conditional on enough balance, checks affected rows, and always either commits or rolls back. The calls below start from Mohan ₹4,000 and Farida ₹3,000.
import mysql from 'mysql2/promise';
const pool = mysql.createPool({
host: 'localhost',
user: 'app',
password: process.env.DB_PASSWORD,
database: 'coaching'
});
async function transfer(fromId, toId, amount) {
const conn = await pool.getConnection();
try {
await conn.beginTransaction();
const [debit] = await conn.execute(
'UPDATE accounts SET balance = balance - ? WHERE account_id = ? AND balance >= ?',
[amount, fromId, amount]);
if (debit.affectedRows !== 1) throw new Error('Insufficient balance or unknown sender');
const [credit] = await conn.execute(
'UPDATE accounts SET balance = balance + ? WHERE account_id = ?',
[amount, toId]);
if (credit.affectedRows !== 1) throw new Error('Unknown receiver');
await conn.commit();
return 'committed';
} catch (err) {
await conn.rollback();
return 'rolled back: ' + err.message;
} finally {
conn.release();
}
}
console.log(await transfer(1, 2, 1500)); // committed
console.log(await transfer(1, 99, 500)); // rolled back: Unknown receiver
console.log(await transfer(2, 1, 100000)); // rolled back: Insufficient balance or unknown sender
// accounts now: Mohan 2500.00, Farida 4500.00 (only the first transfer happened)
In the second call the debit from Mohan had already succeeded inside the transaction when the credit found no account 99; the rollback put his ₹500 back. The ? placeholders also keep the values out of the SQL text, which prevents SQL injection.
Order Plus Stock Update
The centre also sells printed module sets. Placing an order means inserting an order row and reducing stock, and both must happen together. The stock update is written as a single conditional statement, stock = stock - ? WHERE ... AND stock >= ?, so the check and the change happen atomically inside MySQL rather than as a separate "read stock, then decide" step in JavaScript.
CREATE TABLE books (
book_id INT PRIMARY KEY,
title VARCHAR(60) NOT NULL,
price DECIMAL(10,2) NOT NULL,
stock INT NOT NULL
);
CREATE TABLE book_orders (
order_id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT NOT NULL,
book_id INT NOT NULL,
qty INT NOT NULL,
amount DECIMAL(10,2) NOT NULL
);
INSERT INTO books VALUES (1, 'JEE Physics Module Set', 1200.00, 3);
async function placeOrder(studentId, bookId, qty) {
const conn = await pool.getConnection();
try {
await conn.beginTransaction();
const [res] = await conn.execute(
'INSERT INTO book_orders (student_id, book_id, qty, amount) ' +
'SELECT ?, book_id, ?, price * ? FROM books WHERE book_id = ?',
[studentId, qty, qty, bookId]);
const [stock] = await conn.execute(
'UPDATE books SET stock = stock - ? WHERE book_id = ? AND stock >= ?',
[qty, bookId, qty]);
if (stock.affectedRows !== 1) throw new Error('Out of stock');
await conn.commit();
return `order ${res.insertId} committed`;
} catch (err) {
await conn.rollback();
return 'rolled back: ' + err.message;
} finally {
conn.release();
}
}
console.log(await placeOrder(1, 1, 2)); // order 1 committed (stock 3 -> 1)
console.log(await placeOrder(2, 1, 2)); // rolled back: Out of stock (order row 2 undone)
// Two students try to buy the last copy at the same moment:
console.log(await Promise.all([placeOrder(3, 1, 1), placeOrder(4, 1, 1)]));
// [ 'order 3 committed', 'rolled back: Out of stock' ]
// book_orders now holds orders 1 and 3; stock is 0, never -1
Two details in that output are worth understanding. The failed order's row was inserted and then rolled back, so it never appears, but its AUTO_INCREMENT number 2 was used up; gaps in IDs are normal and not a sign of lost data. And in the race for the last copy, InnoDB locked the book row for the first UPDATE, so the second waited, then saw stock 0 and matched no rows. If the code had read the stock with a plain SELECT, decided in JavaScript, and then written stock = 0, both students could have "bought" the same copy. When you genuinely must read a value before deciding, use SELECT ... FOR UPDATE inside the transaction, which locks the row until you commit.
ACID, Letter by Letter
| Letter | Meaning | In the examples | What provides it in MySQL |
|---|---|---|---|
| Atomicity | All statements in the transaction happen, or none do | The failed transfer to account 99 left Mohan's balance unchanged | InnoDB's undo log and ROLLBACK |
| Consistency | A transaction moves the database from one valid state to another; rules are never broken | Balance can't go negative; stock can't go below 0; a payment can't point to a missing student | Constraints (primary, foreign, unique, CHECK, NOT NULL) plus your own correct logic |
| Isolation | Concurrent transactions don't see each other's half-finished work | Two buyers of the last copy did not both succeed | Row locks and MVCC snapshots, tuned by the isolation level |
| Durability | Once COMMIT returns, the change survives a crash or power cut | A committed fee payment is still there after a restart | The redo log, flushed to disk on every commit by default (innodb_flush_log_at_trx_commit = 1) |
Consistency is the one the database cannot fully provide on its own. MySQL enforces the rules you declare, but if your code moves money in the wrong direction inside a perfectly atomic transaction, the result is consistently wrong. Declare every rule you can as a constraint, so the database is a second line of defence.
Isolation Levels
Full isolation would make every transaction wait for every other one, so the SQL standard defines four levels that trade some isolation for concurrency. They are described by which read anomalies they allow. A dirty read sees another transaction's uncommitted change. A non-repeatable read reads the same row twice and gets different values because someone committed in between. A phantom read runs the same range query twice and finds new rows.
| Level | Dirty read | Non-repeatable read | Phantom read | Notes |
|---|---|---|---|---|
READ UNCOMMITTED | Possible | Possible | Possible | Almost never appropriate |
READ COMMITTED | No | Possible | Possible | Each statement sees the latest committed data; default in PostgreSQL, Oracle and SQL Server |
REPEATABLE READ | No | No | Possible by the standard; InnoDB prevents them for plain reads | InnoDB default. Plain SELECTs read a snapshot taken at the first read; locking reads use next-key locks |
SERIALIZABLE | No | No | No | Plain SELECTs become shared-lock reads when autocommit is off; most blocking |
The differences are easy to see with two connections, A and B, on the accounts table. Under the default REPEATABLE READ, A keeps seeing its snapshot even after B commits; under READ COMMITTED it sees B's change immediately; under READ UNCOMMITTED it can even see a change that B later rolls back.
-- REPEATABLE READ (default)
A: START TRANSACTION;
A: SELECT balance FROM accounts WHERE account_id = 1; -- 5000.00
B: UPDATE accounts SET balance = 4200 WHERE account_id = 1; -- autocommit
A: SELECT balance FROM accounts WHERE account_id = 1; -- 5000.00 (same snapshot)
A: COMMIT;
A: SELECT balance FROM accounts WHERE account_id = 1; -- 4200.00
-- READ COMMITTED
A: SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
A: START TRANSACTION;
A: SELECT balance FROM accounts WHERE account_id = 1; -- 4200.00
B: UPDATE accounts SET balance = 3900 WHERE account_id = 1;
A: SELECT balance FROM accounts WHERE account_id = 1; -- 3900.00 (changed mid-transaction)
A: COMMIT;
-- READ UNCOMMITTED: a dirty read
A: SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
B: START TRANSACTION;
B: UPDATE accounts SET balance = 0 WHERE account_id = 1;
A: SELECT balance FROM accounts WHERE account_id = 1; -- 0.00 (never committed!)
B: ROLLBACK;
A: SELECT balance FROM accounts WHERE account_id = 1; -- 3900.00
A snapshot only applies to plain reads. An UPDATE, or a SELECT ... FOR UPDATE, always works on the latest committed row and locks it, which is why the conditional stock update above was safe at the default level. For most applications, leave the default alone and use locking reads or conditional updates where money or stock is involved.
Deadlocks, Briefly
A deadlock happens when two transactions each hold a lock the other needs. Below, A locks Mohan's row and B locks Farida's; then A wants Farida's row and B wants Mohan's. Neither can continue, so InnoDB detects the cycle straight away, rolls back one transaction (the "victim") and lets the other finish.
A: START TRANSACTION;
A: UPDATE accounts SET balance = balance - 100 WHERE account_id = 1; -- locks row 1
B: START TRANSACTION;
B: UPDATE accounts SET balance = balance - 100 WHERE account_id = 2; -- locks row 2
A: UPDATE accounts SET balance = balance + 100 WHERE account_id = 2; -- waits for B
B: UPDATE accounts SET balance = balance + 100 WHERE account_id = 1;
-- B: ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
-- A: its waiting UPDATE now succeeds; A commits
Deadlocks are a normal event in busy systems, not a crash. Handle them in three ways: keep transactions short (never wait for a user, an email or an HTTP call while holding locks), touch rows in a consistent order (for example, always lock the lower account ID first in a transfer), and catch error 1213 in your code and retry the whole transaction a small number of times. SHOW ENGINE INNODB STATUS shows details of the most recent deadlock.
Only InnoDB Is Transactional, and DDL Commits Implicitly
Transactions are a feature of the storage engine, not of MySQL as a whole. InnoDB, the default since MySQL 5.5, supports them. MyISAM, still found in some old databases, does not; its changes are saved immediately and a rollback cannot undo them:
CREATE TABLE fee_log_myisam (id INT, note VARCHAR(30)) ENGINE=MyISAM;
CREATE TABLE fee_log_innodb (id INT, note VARCHAR(30)) ENGINE=InnoDB;
START TRANSACTION;
INSERT INTO fee_log_innodb VALUES (1, 'test');
INSERT INTO fee_log_myisam VALUES (1, 'test');
ROLLBACK;
-- Warning 1196: Some non-transactional changed tables couldn't be rolled back
SELECT (SELECT COUNT(*) FROM fee_log_innodb) AS innodb_rows,
(SELECT COUNT(*) FROM fee_log_myisam) AS myisam_rows;
-- innodb_rows: 0 | myisam_rows: 1
Check an inherited database with SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE(); and convert old tables with ALTER TABLE t ENGINE=InnoDB;. Also know that DDL statements such as CREATE TABLE, ALTER TABLE and DROP TABLE cause an implicit commit: in a test, an UPDATE followed by CREATE TABLE and then ROLLBACK left the update saved. Keep schema changes out of business transactions.
Common Mistakes (and the Fix)
| Mistake | What goes wrong | Fix |
|---|---|---|
| INNER JOIN when the other side may be missing | Students with no payment, or batches with no students, silently disappear | LEFT JOIN from the table whose rows must all appear, plus COALESCE(SUM(...), 0) |
Filtering the right table of a LEFT JOIN in WHERE | Unmatched rows are removed, so it behaves like an INNER JOIN | Move that condition into the ON clause |
| Joining two one-to-many tables, then summing | Fan-out multiplies rows: fees and counts doubled | Aggregate each table in a derived table first, then join; compare against a plain total |
COUNT(column) when you meant rows; trusting AVG with NULLs | Undercounts and averages that skip missing values | COUNT(*) for rows; COALESCE where NULL should mean 0 |
Functions, leading % or numbers on indexed columns: YEAR(paid_on), LIKE '%4321', phone = 9800004321 | Index ignored, type = ALL, while results still look correct | Date ranges, prefix searches, and values of the column's own type; confirm with EXPLAIN |
Running COMMIT after a failed statement, or using autocommit for multi-step changes | Half a transfer or half an order is saved | Wrap related writes in one transaction; on any error ROLLBACK; check affected rows |
My Opinion
Start every report from the thing you are reporting on, and default to LEFT JOIN from it. "Fee status of each student" starts from students; "collection per batch" starts from batches. Switch to INNER JOIN only when you have decided that unmatched rows should disappear. Most silent reporting bugs are the opposite choice made by habit.
Always check a report against a total you trust. Before sharing any report that sums money, compare its grand total with SELECT SUM(amount) FROM payments for the same period. It takes one line, and it catches fan-out, missing rows and wrong date filters before anyone acts on the numbers.
Make EXPLAIN a habit, not an emergency tool. Run it on every new query that touches a table which will grow, and look at type, key and rows. A type = ALL on a table of students, orders or payments is worth fixing while the table is still small and the fix is cheap.
Index for real queries, and keep the list short. Index primary keys, foreign keys, login fields and the filters your busiest screens use. Prefer one well-ordered composite index over several single-column ones, and drop indexes that no query needs.
Put anything involving money or stock in a transaction with explicit rollback. Use conditional updates (WHERE stock >= ?) and check affected rows, keep transactions short, and retry on deadlock. Leave the isolation level at InnoDB's default unless you can name the anomaly you need to prevent.
Try It Yourself
Use the sample database from the top of this page. Write each query before opening the answers, then run it to check.
1. Write a query that lists every batch with the number of students in it,
showing 0 for empty batches.
2. Which join type answers "show each payment with the student's name"?
And "show every student with their total paid, including those who paid nothing"?
3. A query joins students to payments and to test_results and sums the payments.
Aarav's total is 90000 instead of 45000. What is the problem, and how do you fix it?
4. Without running it, what do COUNT(*), COUNT(phone) and COUNT(DISTINCT city)
return on the original six students?
5. Write a query for the total collected in October 2026 that can use an index
on paid_on.
6. You have an index on (city, name). Which of these can use it?
a) WHERE city = 'Jharia' b) WHERE name = 'Rohan'
c) WHERE name = 'Rohan' AND city = 'Jharia'
7. EXPLAIN shows type = ALL for WHERE phone = 9431000003 although phone is indexed.
Why, and what is the fix?
8. Inside a transaction, the first UPDATE succeeds and the second fails with an
error. If your code now runs COMMIT, what is saved?
Show answers
1. SELECT b.batch_name, COUNT(s.student_id) AS students
FROM batches b
LEFT JOIN students s ON s.batch_id = b.batch_id
GROUP BY b.batch_id, b.batch_name;
-- JEE Morning 3, NEET Evening 2, Class 10 Foundation 0
2. INNER JOIN (every payment has a student). LEFT JOIN from students, with
COALESCE(SUM(p.amount), 0) so Kunal and Priya show 0.00.
3. Fan-out: 2 payments x 2 test results = 4 rows, so each payment is counted twice.
Aggregate payments and test_results separately in derived tables, then join.
4. 6, 5 (Sana has no phone) and 3 (Dhanbad, Jharia, Sindri).
5. SELECT COALESCE(SUM(amount), 0) FROM payments
WHERE paid_on >= '2026-10-01' AND paid_on < '2026-11-01';
-- 86000.00 on the sample data
6. a) yes (leftmost prefix) b) no, it needs city first c) yes, the order of
conditions in WHERE does not matter.
7. The value is a number but phone is a VARCHAR, so MySQL converts every stored
phone before comparing (warning 1739). Quote it: WHERE phone = '9431000003'.
8. The first UPDATE. A failed statement rolls back only itself; the transaction
stays open. Catch the error and run ROLLBACK instead.
Frequently Asked Questions
Is a JOIN slower than storing everything in one table?
A join on indexed key columns is very cheap: for each row, MySQL does one index lookup on the other table (the eq_ref plan you saw). The cost of one big table with repeated data shows up elsewhere, in larger tables, slower writes and data that contradicts itself. Normalise first and join; denormalise a specific column only when EXPLAIN and real measurements show a join is the bottleneck.
Should I use a subquery or a JOIN?
Use whichever states the question most clearly; MySQL 8's optimiser rewrites many subqueries into joins anyway. Prefer NOT EXISTS or LEFT JOIN ... IS NULL over NOT IN when NULLs are possible, and use derived tables to aggregate before joining when fan-out is a risk. When in doubt, compare both with EXPLAIN.
How many indexes should one table have?
There is no fixed number. A read-heavy table such as a product catalogue can reasonably carry several indexes; a write-heavy log table should carry as few as possible. Start with the primary key, foreign keys and the filters your main screens use, then add indexes only when a real query needs one.
Do I need transactions for a single INSERT or UPDATE?
No. With autocommit on, a single statement is already atomic: it either completes fully or not at all. You need an explicit transaction when two or more statements must succeed or fail together, such as debit plus credit, or order plus stock update.
Can I change the isolation level for one transaction only?
Yes. SET TRANSACTION ISOLATION LEVEL READ COMMITTED; without SESSION or GLOBAL applies to the next transaction only, and SET SESSION TRANSACTION ... applies to the rest of the connection. Changing the global default affects every application on the server, so do it only with a clear reason.
Key Takeaways
- INNER JOIN keeps only matching rows; LEFT JOIN keeps every row of the left table. Choose by asking whether rows without a match should still appear.
- MySQL has no FULL OUTER JOIN: combine a LEFT JOIN and a RIGHT JOIN with
UNION, or withUNION ALLplus anIS NULLfilter when duplicates are legitimate. - Joining one row to two independent one-to-many tables causes fan-out; aggregate each table first, then join.
COUNT(*)counts rows,COUNT(col)skips NULLs, aggregates return NULL on no rows, andHAVINGfilters groups whileWHEREfilters rows.- EXPLAIN's
type,keyandrowsshow whether an index is used; a phone search went fromALLwith 5,006 rows torefwith 1 row afterCREATE INDEX. - Composite indexes work on their leftmost prefix; functions on the column, leading-% LIKE and type mismatches disable an index.
- Indexes speed reads and slow writes, so index the queries you actually run.
- Transactions make multi-step changes all-or-nothing, but only on InnoDB, and only if your code rolls back on every error. InnoDB's default isolation level is REPEATABLE READ.
Next, use these tools to design a full schema in How to Design a Scalable E-Commerce & Multi-Vendor Marketplace Database, then tune it with MySQL Performance Tuning, Security & Backup.
References
- MySQL 8.4 Reference Manual: JOIN Clause
- MySQL 8.4 Reference Manual: EXPLAIN Output Format
- MySQL 8.4 Reference Manual: Multiple-Column Indexes
- MySQL 8.4 Reference Manual: Transaction Isolation Levels

0 Comments