# `INSERT` a Row Into a Table with Clustered Index for Primary Key

![`smart`](ClusteredPrimaryKeyTableINSERT01_aa_002_20230905.png)

```7
tidb> DESC t_clustered;
+-------+-------------+------+------+---------+-------+
| Field | Type        | Null | Key  | Default | Extra |
+-------+-------------+------+------+---------+-------+
| id    | int(11)     | NO   | PRI  | NULL    |       |
| name  | varchar(20) | YES  |      | NULL    |       |
| tel   | varchar(16) | YES  |      | NULL    |       |
+-------+-------------+------+------+---------+-------+

tidb> SELECT TABLE_NAME, TIDB_PK_TYPE FROM information_schema.tables 
  WHERE table_schema = 'test' AND table_name in ('t_clustered');
+-------------+--------------+
| TABLE_NAME  | TIDB_PK_TYPE |
+-------------+--------------+
| t_clustered | ~!@CLUSTERED@!~    |
+-------------+--------------+

tidb> SELECT id, name, tel FROM t_clustered ORDER BY id;
+----+-------+------------+
| id | name  | tel        |
+----+-------+------------+
|  1 | Tom   | 7073958726 |
|  2 | Jack  | 4782733578 |
|  3 | Frank | 147810546  |
|  4 | Tony  | 335451143  |
|  5 | Jim   | 1390811212 |
|  6 | Sam   | 1579915611 |
+----+-------+------------+

tidb> INSERT INTO t_clustered VALUES (7, 'Andy', 18922118921);
...
```
------------------------------------------------------------------------------
The slide shows a table `t_clustered` with 6 rows, which is a table with clustered index for primary key. The primary key is on column `id`.

On the slide, we have distributed 6 rows across two TiKV Regions on two TiKV Stores. However, in real-world scenarios, these rows are usually stored in a single TiKV Region since it does not exceed 96 MB (the default size for a TiKV Region) in size. And, since you are using TiDB, it is likely that you want to store 6 billion rows instead of just 6 rows. Therefore, it is common to have rows from a single table spread out across multiple TiKV Stores. Using only a limited number of rows for the illustration effectively serves the purpose of our concept study.

```
CREATE TABLE t_clustered (
 id INT PRIMARY KEY CLUSTERED,
 name VARCHAR(20),
 tel VARCHAR(16)
);
INSERT INTO t_clustered VALUES (1, 'Tom', '7073958726');
INSERT INTO t_clustered VALUES (2, 'Jack', '4782733578');
INSERT INTO t_clustered VALUES (3, 'Frank', '147810546');
INSERT INTO t_clustered VALUES (4, 'Tony', '335451143');
INSERT INTO t_clustered VALUES (5, 'Jim', '1390811212');
INSERT INTO t_clustered VALUES (6, 'Sam', '1579915611');
```

Database client has issued a `INSERT` statement:
```
INSERT INTO t_clustered VALUES (7, 'Andy', 18922118921);
```

Then, two important steps executed by the TiDB server instance in a specific order:
`1.` Queries which TiKV region that has a key range containing the actual key. Note that on the diagram the pointer is special, normally the information is cached in TiDB server, so no actual query is executed from TiDB server to PD server during the INSERT operation. 
`2.` Depending on auto commit, isolation level and transaction mode (optimistic or pessimistic) it may do a read first to check for duplicate key, if there is no duplicated entry, stores the newly created row in the TiKV Region where the table is located.

For a table with clustered index for primary key, the table and the primary key index use the same storage structure, which eliminates the need for an additional entry in the primary key index. As a result, it saves both storage space and IO resources.