# Working with Joins - Sample Schema Setup

```
tidb>SHOW CREATE TABLE TA\G
*************************** 1. row ***************************
       Table: TA
Create Table: CREATE TABLE `TA` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `col1` int(4) NOT NULL,
  `col2` int(4) NOT NULL,
  `col3` int(4) NOT NULL,
  `col4` varchar(50) DEFAULT NULL,
  `col5` datetime DEFAULT NULL,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
  KEY `idx_col1_col2` (`col1`,`col2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=30256
1 row in set (0.00 sec)

tidb>SHOW CREATE TABLE TB\G
*************************** 1. row ***************************
       Table: TB
Create Table: CREATE TABLE `TB` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `col1` int(4) NOT NULL,
  `col2` int(4) NOT NULL,
  `col3` int(4) NOT NULL,
  `col4` varchar(50) DEFAULT NULL,
  `col5` datetime DEFAULT NULL,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
  KEY `idx_col1` (`col1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=30001
1 row in set (0.01 sec)
```
```
tidb>SHOW CREATE TABLE TC\G
*************************** 1. row ***************************
       Table: TC
Create Table: CREATE TABLE `TC` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `col1` int(4) NOT NULL,
  `col2` int(4) NOT NULL,
  `col3` int(4) NOT NULL,
  `col4` varchar(50) DEFAULT NULL,
  `col5` datetime DEFAULT NULL,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
  KEY `idx_col2` (`col2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=30001
1 row in set (0.00 sec)

tidb>SHOW CREATE TABLE TD\G
*************************** 1. row ***************************
       Table: TD
Create Table: CREATE TABLE `TD` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `col1` int(4) NOT NULL,
  `col2` int(4) NOT NULL,
  `col3` int(4) NOT NULL,
  `col4` varchar(50) DEFAULT NULL,
  `col5` datetime DEFAULT NULL,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
  KEY `idx_col2` (`col2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=30001
1 row in set (0.00 sec)
```
----------------------------------------------------
Let's examine an example of using an `optimizer hint` to optimize table joins. The table creation statements for tables `TA`, `TB`, `TC`, and `TD` are provided in the slides. Specifically, table `TA` has a composite index on columns `col1` and `col2`, table `TB` has an index on column `col1`, and both tables `TC` and `TD` have an index on column `col2`. The query statement is as follows:

```
CREATE TABLE TA(
id bigint(20) unsigned AUTO_INCREMENT NOT NULL,
col1 int(4) NOT NULL,
col2 int(4) NOT NULL,
col3 int(4) NOT NULL,
col4 varchar(50) DEFAULT NULL,
col5 datetime DEFAULT NULL,
PRIMARY KEY(id) CLUSTERED
);

CREATE TABLE TB(
id bigint(20) unsigned AUTO_INCREMENT NOT NULL,
col1 int(4) NOT NULL,
col2 int(4) NOT NULL,
col3 int(4) NOT NULL,
col4 varchar(50) DEFAULT NULL,
col5 datetime DEFAULT NULL,
PRIMARY KEY(id) CLUSTERED
);

CREATE TABLE TC(
id bigint(20) unsigned AUTO_INCREMENT NOT NULL,
col1 int(4) NOT NULL,
col2 int(4) NOT NULL,
col3 int(4) NOT NULL,
col4 varchar(50) DEFAULT NULL,
col5 datetime DEFAULT NULL,
PRIMARY KEY(id) CLUSTERED
);

CREATE TABLE TD(
id bigint(20) unsigned AUTO_INCREMENT NOT NULL,
col1 int(4) NOT NULL,
col2 int(4) NOT NULL,
col3 int(4) NOT NULL,
col4 varchar(50) DEFAULT NULL,
col5 datetime DEFAULT NULL,
PRIMARY KEY(id) CLUSTERED
);

alter table TA add index idx_col1_col2(col1, col2);

alter table TB add index idx_col1(col1);

alter table TC add index idx_col2(col2);

alter table TD add index idx_col2(col2);

```

Insert data into the table, the code is as follows:

```
for i in `seq 1000`; do mysql -h<IP OF YOUR TiDB SERVER> -P<PORT OF YOUR TiDB SERVER> -uroot -p<YOUR PASSWORD> -e"INSERT INTO test.TA (col1, col2, col3, col4, col5) VALUES(0, 0, 0, substring(MD5(RAND()), 1, 5), now())"; done;
for i in `seq 1200`; do mysql -h<IP OF YOUR TiDB SERVER> -P<PORT OF YOUR TiDB SERVER> -uroot -p<YOUR PASSWORD> -e"INSERT INTO test.TB (col1, col2, col3, col4, col5) VALUES(0, 0, 0, substring(MD5(RAND()), 1, 5), now())"; done;
for i in `seq 1500`; do mysql -h<IP OF YOUR TiDB SERVER> -P<PORT OF YOUR TiDB SERVER> -uroot -p<YOUR PASSWORD> -e"INSERT INTO test.TC (col1, col2, col3, col4, col5) VALUES(0, 0, 0, substring(MD5(RAND()), 1, 5), now())"; done;
for i in `seq 2000`; do mysql -h<IP OF YOUR TiDB SERVER> -P<PORT OF YOUR TiDB SERVER> -uroot -p<YOUR PASSWORD> -e"INSERT INTO test.TD (col1, col2, col3, col4, col5) VALUES(0, 0, 0, substring(MD5(RAND()), 1, 5), now())"; done;
```
for i in `seq 1000`; do mysql -h10.90.1.38 -P4000 -uroot -ptidb -e"INSERT INTO test.TA (col1, col2, col3, col4, col5) VALUES(0, 0, 0, substring(MD5(RAND()), 1, 5), now())"; done;
for i in `seq 1200`; do mysql -h10.90.1.38 -P4000 -uroot -ptidb -e"INSERT INTO test.TB (col1, col2, col3, col4, col5) VALUES(0, 0, 0, substring(MD5(RAND()), 1, 5), now())"; done;
for i in `seq 1500`; do mysql -h10.90.1.38 -P4000 -uroot -ptidb -e"INSERT INTO test.TC (col1, col2, col3, col4, col5) VALUES(0, 0, 0, substring(MD5(RAND()), 1, 5), now())"; done;
for i in `seq 2000`; do mysql -h10.90.1.38 -P4000 -uroot -ptidb -e"INSERT INTO test.TD (col1, col2, col3, col4, col5) VALUES(0, 0, 0, substring(MD5(RAND()), 1, 5), now())"; done;

Connect to the database and run:

```
update TA set col1 = id % 100;

update TA set col2 = id % 100;

update TB set col1 = id % 100;

update TC set col2 = id % 100;

update TD set col2 = id % 100 + 1000;

update TD set col2 = 1 where id = 1;

analyze table TA, TB, TC, TD;
```