MySQL InnoDB all data is stored in the PRIMARY key (clustered index). Each row in the table is physically ordered by the PRIMARY KEY

'CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `firstname` varchar(100) NOT NULL DEFAULT '''',
  `lastname` varchar(100) NOT NULL DEFAULT '''',
  PRIMARY KEY (`id`),
  KEY `fn_ln_index` (`firstname`,`lastname`)
) ENGINE=InnoDB AUTO_INCREMENT=458600003 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci'; 

 explain SELECT COUNT(id) FROM t1;
'1', 'SIMPLE', 't1', NULL, 'index', NULL, 'fn_ln_index', '804', NULL, '425588929', '100.00', 'Using index'

explain SELECT COUNT(id) FROM t1 FORCE INDEX (PRIMARY); 
1    SIMPLE    t1        index        PRIMARY    4        425588929    100.00    Using index  why the default is not the primary key id,

 

explain SELECT COUNT(id) FROM t1 FORCE INDEX (PRIMARY); 
'1', 'SIMPLE', 't1', NULL, 'index', NULL, 'PRIMARY', '4', NULL, '456515090', '100.00', 'Using index'

 

 

 

1. InnoDB Index Storage Basics

• In InnoDB, all data is stored in the PRIMARY key (clustered index).
  Each row in the table is physically ordered by the PRIMARY KEY.
  So a clustered index entry looks like this:

 

[id] + [firstname] + [lastname] + (all other columns)

That means the PRIMARY index contains the entire row.

---

• A secondary index (like fn_ln_index(firstname, lastname)) only stores:

  • The indexed columns: firstname, lastname

  • Plus the PRIMARY KEY (id) as a pointer to the full row

So one entry in fn_ln_index looks like:

 

[firstname] + [lastname] + [id]

 This is much smaller than a PRIMARY index entry (because it does not store all other columns).

---

2. What happens during COUNT(id)

When you run:

 

SELECT COUNT(id) FROM t1;

• MySQL only needs to know:
  "How many rows exist?"

• It does not need the actual data values, it just needs to traverse an index that has one entry per row.

Both:

• PRIMARY key (clustered index)

• fn_ln_index

… satisfy this requirement (because both contain one entry per row).

---

3. Optimizer’s Cost Model

Now MySQL asks:

     Which index is cheaper to scan through completely?

• PRIMARY key entries are big: they store the entire row.

• Secondary index entries are small: they only store the indexed columns + PK.

Since scanning requires reading data pages from disk/memory, the optimizer prefers the smaller index (fewer bytes, fewer pages).

---

4. Example (Numbers)

Suppose each column is VARCHAR(100).

• A row in PRIMARY index = id (4 bytes) + firstname (100 bytes) + lastname (100 bytes)
  → ~204 bytes per entry (ignoring row overhead).

• A row in secondary index = firstname (100) + lastname (100) + id (4)
  → ~204 bytes as well, but:
  If the table had many other columns (say 20 extra columns), PRIMARY entries would be much larger, while the secondary index still only stores (firstname, lastname, id).

That’s why in practice the optimizer often prefers a secondary index: fewer columns = smaller entry = fewer I/O pages.

---

5. Why “Using index” appears

In the EXPLAIN output you saw:

 

Using index

That means MySQL was able to satisfy the query by reading only from the index (a covering index), without touching the table rows.
This is efficient.

---

✅ Final Principle:
The optimizer prefers the secondary index for COUNT(id) because the secondary index entries are usually narrower than clustered index entries, making full index scans faster (less data to read).

 

 

 

 

why mysql innodb,PRIMARY key index as a big, wide box — it contains the whole row:

 

1. What is a Clustered Index?

InnoDB tables are clustered by the PRIMARY KEY.
That means:

• The PRIMARY KEY index is not just an index pointing to rows.

• It is the actual storage of the rows, ordered by the PRIMARY KEY.

So when you say:

 

PRIMARY KEY (id)

InnoDB will physically organize the table on disk like this:

 

B+Tree (id) → leaf nodes → contain full row data

---

2. Why does it store the whole row?

Because InnoDB has no “heap table” (like MyISAM did).
InnoDB requires every row to be stored inside a clustered index.
The leaf nodes of the PRIMARY index are the table data itself.

That’s why the PRIMARY index is “wide”:

• Key = id

• Leaf = full row (firstname, lastname, all other columns)

---

Example (visual)

PRIMARY index = clustered, full row

 

B+Tree (id) ├─ Node: id=1 → (id=1, firstname='Tom', lastname='Lee', col3, col4, ...) ├─ Node: id=2 → (id=2, firstname='Ana', lastname='Chen', col3, col4, ...) └─ Node: id=3 → (id=3, firstname='Ali', lastname='Hassan', col3, col4, ...)

The leaf is the entire row.

---

Secondary index = smaller

 

B+Tree (firstname, lastname) ├─ Node: (Ana, Chen) → pointer to PRIMARY (id=2) ├─ Node: (Ali, Hassan) → pointer to PRIMARY (id=3) └─ Node: (Tom, Lee) → pointer to PRIMARY (id=1)

Leaves contain only:

• Indexed columns (firstname, lastname)

• Primary key (id) as a “bookmark” to the real row

---

3. Why does this design exist?

• ✅ Faster lookups by primary key (directly fetches row, no extra lookup needed).

• ✅ Guarantees table rows are stored in PK order (helpful for range queries).

• ❌ But the PRIMARY index is wide — scanning it means reading all row data.

---

4. Analogy

Think of:

• PRIMARY index = a big book where every entry includes the full biography.

• Secondary index = a lighter book with just “Name + page number”, pointing back to the full biography in the primary book.

---

✅ Answer:
MySQL InnoDB’s PRIMARY key index contains the whole row because InnoDB implements clustered storage — the table itself is the PRIMARY key index.
This design makes PK lookups and range scans efficient, but makes the PRIMARY index “wide”.