# Subquery Optimization - Example Schema Setup
```
tidb>SHOW CREATE TABLE T1\G
***************************1.IOW ***************************
       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=30001
1 row in set (0.00 sec)

tidb>SHOW CREATE TABLE T2\G
***************************1.IOW ***************************
       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=30001
1 row in set (0.00 sec)
```

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

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

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

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

----------------------------------------------------
Let's examine an example involving Quantified Comparison subqueries. Table 'T1' contains 10 rows, while table 'T2' has 10,000 rows. In table 'T1', the 'col1' column holds unique data for each row, and similarly, the 'col1' column in table 'T2' also contains unique data for every row. Both tables have indexed 'col1' columns. We will execute the following subquery operation:

```
SELECT T1.col4, T1.col5, (SELECT SUM(T2.col2) FROM T2 WHERE T2.col1 = T1.col1) FROM T1;
```

Below are the statements for creating 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 10`; 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 10000`; 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;
UPDATE T2 SET col2=id;

ANALYZE TABLE T1;
ANALYZE TABLE T2;
```