# Working with Indexes - Sample 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_col2` (`col2`),
  KEY `idx_col3` (`col3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=120001
1 row in set (0.00 sec)

tidb> SELECT COUNT(*) FROM T1;
+--------------+
| COUNT(*)     |
+--------------+
|   100000     |
+--------------+
1 row in set (0.01 sec)

tidb> SELECT COUNT(DISTINCT(col1)), COUNT(DISTINCT(col2)), COUNT(DISTINCT(col3)) FROM T1;
+-----------------------+-----------------------+-----------------------+
| COUNT(DISTINCT(col1)) | COUNT(DISTINCT(col2)) | COUNT(DISTINCT(col3)) |
+-----------------------+-----------------------+-----------------------+
|                     5 |                   100 |                100000 |
+-----------------------+-----------------------+-----------------------+
1 row in set (0.02 sec)
```
------------------------------------------------------------------------------
Here is an example of using an `optimizer hint` to optimize index selection: Table `T1` contains a total of 100,000 rows. There is an index `idx_col2` on the `col2` column and another index `idx_col3` on the `col3` column. The data distribution for `col1` consists of 5 distinct values, each with 20,000 rows. The distribution of `col2` comprises 100 distinct values, each with 1,000 rows. Meanwhile, `col3` has 100,000 distinct values.

The SQL statement for creating the table is as follows:

```
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
);

ALTER TABLE T1 ADD INDEX idx_col2(col2);
ALTER TABLE T1 ADD INDEX idx_col3(col3);
```

To insert data into the table, use the following code:

```
for i in `seq 100000`; 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;
```

Then connect to the database and run:

```
UPDATE T1 SET col1 = 1000 WHERE id BETWEEN 1 AND 20000;
UPDATE T1 SET col1 = 2000 WHERE id BETWEEN 20001 AND 40000;
UPDATE T1 SET col1 = 3000 WHERE id BETWEEN 40001 AND 60000;
UPDATE T1 SET col1 = 4000 WHERE id BETWEEN 60001 AND 80000;
UPDATE T1 SET col1 = 5000 WHERE id BETWEEN 80001 AND 100000;

UPDATE T1 SET col3 = id;
UPDATE T1 SET col2 = id%100;

SHOW CREATE TABLE T1\G

SELECT COUNT(*) FROM T1;

SELECT COUNT(DISTINCT(col1)), COUNT(DISTINCT(col2)), COUNT(DISTINCT(col3)) FROM T1;

ANALYZE TABLE T1;
```