# Subquery Optimization - Example Schema Setup
```
tidb>SHOW CREATE TABLE T1\G
***************************1.row ***************************
       Table:T1
Create Table: CREATE TABLE`T1` (
  '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' varchar(50) DEFAULT NULL,
  `col6' datetime DEFAULT NULL,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
  KEY `idx_col1` (`col1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=300O1
1 row in set(0.01 sec)

tidb>SHOW CREATE TABLE T2\G
***************************1.row ***************************
       Table:T2
Create Table: CREATE TABLE`T2` (
  '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` varchar(50) DEFAULT NULL,
  `col6` datetime DEFAULT NULL,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
  KEY `idx_col1` (`col1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=300011 
row in set(0.00 sec)
```

```
tidb>SELECT COUNT(*)FROM T1;
+----------+
| COUNT(*) |
+----------+
|    10000 |
+----------+
1 row in set (0.00 sec)

tidb>SELECT COUNT(*)FROM T2;
+----------+
| COUNT(*) |
+----------+
|       10 |
+----------+
1 row in set (0.00 sec)

tidb>SELECT COUNT(DISTINCT(col1)) FROM T1;
+-----------------------+
| COUNT(DISTINCT(col1)) |
+-----------------------+
|                 10000 |
+-----------------------+
1 row in set (0.00 sec)

tidb>SELECT COUNT(DISTINCT(col1)) FROM T2;
+----------------------+
| COUNT(DISTINCT(co11))|
+----------------------+
|                   10 |
+----------------------+
1 row in set (0.00 sec)
```

----------------------------------------------------
There is an example of existential test subqueries. Table `T1` has 10,000 rows of data, and table `T2` has 10 rows of data. The `col1` column in table `T1` has different data in each row, and the `col1` column in table `T2` also has different data in each row, and both columns have an index. Now we perform a subquery operation as follows: "SELECT * FROM T1 WHERE EXISTS( SELECT COl1 FROM T2 WHERE T2.col1 = T1.c011 )

Here is an example of an existential test subquery. Table `T1` contains 10,000 rows of data, while table `T2` holds 10 rows. The `col1` column in both tables features unique data in each row, and both columns are indexed. We can execute the following subquery operation: 

```
SELECT * FROM T1 WHERE EXISTS( SELECT COl1 FROM T2 WHERE T2.col1 = T1.c011 )
```

The following statements outline the creation of tables and indexes:
```
CREATE TABLE T1(
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 varchar(50) DEFAULT NULL,
col6 datetime DEFAULT NULL,
PRIMARY KEY(id) CLUSTERED
);

CREATE TABLE T2(
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 varchar(50) DEFAULT NULL,
col6 datetime DEFAULT NULL,
PRIMARY KEY(id) CLUSTERED
);

ALTER TABLE T1 ADD INDEX idx_col1(col1);
ALTER TABLE T2 ADD INDEX idx_col1(col1);
```

Below are the statements for importing data:

```
for i in `seq 10000`; do mysql -h<IP OF YOUR TiDB SERVER> -P<PORT OF YOUR TiDB SERVER> -uroot -p<YOUR PASSWORD> -e"INSERT INTO test.T1 (col1, col2, col3, col4, col5, col6) VALUES(0, 0, 0, substring(MD5(RAND()), 1, 5), substring(MD5(RAND()), 1, 10), now())"; done;

for i in `seq 10`; do mysql -h<IP OF YOUR TiDB SERVER> -P<PORT OF YOUR TiDB SERVER> -uroot -p<YOUR PASSWORD> -e"INSERT INTO test.T2 (col1, col2, col3, col4, col5, col6) VALUES(0, 0, 0, substring(MD5(RAND()), 1, 5), substring(MD5(RAND()), 1, 10), now())"; done;
```
```

Below are the statements for updating data and analyzing table:

```
UPDATE T1 SET col1=id;
UPDATE T2 SET col1=id;

ANALYZE TABLE T1;
ANALYZE TABLE T2;
```