# Multi-Table Join Case Study Background

+ Table definition:

```11
CREATE TABLE ~!@t1@!~ (
 id int(11) NOT NULL AUTO_INCREMENT,
 k int(11) NOT NULL DEFAULT '0',
 c char(120) NOT NULL DEFAULT '',
 pad char(60) NOT NULL DEFAULT '',
 PRIMARY KEY (id) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

CREATE TABLE ~!@t2@!~ (
 id int(11) NOT NULL AUTO_INCREMENT,
 k int(11) NOT NULL DEFAULT '0',
 c char(120) NOT NULL DEFAULT '',
 pad char(60) NOT NULL DEFAULT '',
 PRIMARY KEY (id) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
```

------------------------------------------------------------

Case background

When we use databases online, we often encounter SQL related to table join query. Here, we introduce the SQL tuning method and the application scenario and differences of various join algorithms in the following cases.

Specific case

We have two tables here. The table schema is as follows:

```
CREATE TABLE t1 (
 id int(11) NOT NULL AUTO_INCREMENT,
 k int(11) NOT NULL DEFAULT '0',
 c char(120) NOT NULL DEFAULT '',
 pad char(60) NOT NULL DEFAULT '',
 PRIMARY KEY (id) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

CREATE TABLE t2 (
 id int(11) NOT NULL AUTO_INCREMENT,
 k int(11) NOT NULL DEFAULT '0',
 c char(120) NOT NULL DEFAULT '',
 pad char(60) NOT NULL DEFAULT '',
 PRIMARY KEY (id) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
```