Mysql: Remove heavy data with forced index

Database version:

[Email protected]mysqltest ~]# mysql-u root-p123456

Welcome to the MySQL Monitor. Commands End With; or \g.

Your MySQL Connection ID is 389805

Server version:5.1.73-community MySQL Community Server (GPL)

Copyright (c) and/or, Oracle, its affiliates. All rights reserved.

Oracle is a registered trademark of the Oracle Corporation and/or its

Affiliates. Other names trademarks of their respective

Owners.

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

Mysql>

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| Information_schema |

| Cacti |

| Centreon |

| Centreon_status |

| Centreon_storage |

| MySQL |

| syslog |

| Test |

+--------------------+

8 rows in Set (0.01 sec)

1. Create a B table used in the experiment

mysql> use test;

Database changed

Mysql> Show tables;

Empty Set (0.00 sec)

Mysql> CREATE Table A (A1 char (TEN), A2 char (TEN), A3 char (10));

Query OK, 0 rows affected (0.08 sec)

Mysql> INSERT into a values (' 1 ', ' 2 ', ' 3 ');

Query OK, 1 row Affected (0.00 sec)

mysql> INSERT into a select * from A;

Query OK, 1 row Affected (0.00 sec)

Records:1 duplicates:0 warnings:0

。

。

。

。

mysql> INSERT into a select * from A;

Query OK, 4194304 rows affected (7.85 sec)

records:4194304 duplicates:0 warnings:0

mysql> INSERT into a select * from A;

Query OK, 8388608 rows Affected (27.81 sec)

records:8388608 duplicates:0 warnings:0

2 CREATE TABLE B:

Mysql> CREATE TABLE B (B1 char (Ten), B2 char (Ten), B3 char (10));

Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO b select * from A;

Query OK, 16777216 rows affected (1 min 6.18 sec)

records:16777216 duplicates:0 warnings:0

Mysql>

Mysql>

Mysql> commit;

Query OK, 0 rows Affected (0.00 sec)

Mysql>

Mysql>

Mysql> SELECT * from B limit 10;

+------+------+------+

| B1 | B2 | B3 |

+------+------+------+

| 1 | 2 | 3 |

| 1 | 2 | 3 |

| 1 | 2 | 3 |

| 1 | 2 | 3 |

| 1 | 2 | 3 |

| 1 | 2 | 3 |

| 1 | 2 | 3 |

| 1 | 2 | 3 |

| 1 | 2 | 3 |

| 1 | 2 | 3 |

+------+------+------+

Rows in Set (0.04 sec)

3 Join B table not the same data

mysql> INSERT into B values (' 4 ', ' 5 ', ' 6 ');

Query OK, 1 row affected (0.01 sec)

mysql> INSERT into B values (' 4 ', ' 5 ', ' 6 ');

Query OK, 1 row Affected (0.00 sec)

mysql> INSERT into B values (' 4 ', ' 5 ', ' 6 ');

Query OK, 1 row Affected (0.00 sec)

mysql> INSERT into B values (' 4 ', ' 5 ', ' 6 ');

Query OK, 1 row Affected (0.00 sec)

Mysql>

mysql> INSERT into B values (' 4 ', ' 5 ', ' 6 ');

Query OK, 1 row Affected (0.00 sec)

Mysql> Commit

;

Query OK, 0 rows Affected (0.00 sec)

Mysql>

Mysql>

Mysql>

4 View a B table number of data rows

Mysql> Select COUNT (1) from B;

+----------+

| COUNT (1) |

+----------+

| 16777224 |

+----------+

1 row in Set (0.00 sec)

Mysql> Select COUNT (1) from A;

+----------+

| COUNT (1) |

+----------+

| 16777216 |

+----------+

1 row in Set (0.00 sec)

5 Creating a C table

Mysql> CREATE TABLE C (C1 char (TEN), C2 char (Ten), C3 char (10));

Query OK, 0 rows affected (0.31 sec)

6 Creating a temporary temp table

mysql> CREATE TABLE Temp SELECT * from C where 1=2;

Query OK, 0 rows affected (0.06 sec)

records:0 duplicates:0 warnings:0

Mysql> SELECT * from temp;

Empty Set (0.00 sec)

mysql> desc temp;

+-------+----------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+----------+------+-----+---------+-------+

| C1 | char (10) |     YES | |       NULL | |

| C2 | char (10) |     YES | |       NULL | |

| C3 | char (10) |     YES | |       NULL | |

+-------+----------+------+-----+---------+-------+

3 Rows in Set (0.02 sec)

7 Creating an index for B table

Mysql> CREATE index ind_b_b1 on B (B1);

Query OK, 16777224 rows affected (2 min 9.14 sec)

records:16777224 duplicates:0 warnings:0

mysql> desc B;

+-------+----------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+----------+------+-----+---------+-------+

| B1 | char (10) | YES | MUL |       NULL | |

| B2 | char (10) |     YES | |       NULL | |

| B3 | char (10) |     YES | |       NULL | |

+-------+----------+------+-----+---------+-------+

3 Rows in Set (0.02 sec)

8 Inserting a B table data into the temp table

mysql> INSERT INTO temp select * from A;

Query OK, 16777216 rows Affected (29.84 sec)

records:16777216 duplicates:0 warnings:0

mysql> INSERT INTO temp select * from B;

Query OK, 16777224 rows Affected (59.79 sec)

records:16777224 duplicates:0 warnings:0

Mysql>

Mysql>

Mysql> commit;

Query OK, 0 rows Affected (0.00 sec)

Mysql> Select COUNT (1) from temp;

+----------+

| COUNT (1) |

+----------+

| 33554440 |

+----------+

1 row in Set (0.00 sec)

9 Creating a federated index forcing the index to remove duplicate data

Mysql> CREATE INDEX ind_temp_c123 on temp (c1, C2, C3);

Query OK, 33554440 rows affected (6 min 57.80 sec)

records:33554440 duplicates:0 warnings:0

Mysql>

Mysql>

Mysql> Explain select C1, C2, Max (C3) from Temp Force index (IND_TEMP_C123) group by C1, C2;

+----+-------------+-------+-------+---------------+---------------+---------+------+------+------------------- -------+

| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |

+----+-------------+-------+-------+---------------+---------------+---------+------+------+------------------- -------+

| 1 | Simple | Temp | Range | NULL | ind_temp_c123 | 22 |    NULL | 3 | Using Index for Group-by |

+----+-------------+-------+-------+---------------+---------------+---------+------+------+------------------- -------+

1 row in Set (0.00 sec)

Mysql> Select COUNT (*) from temp;

+----------+

| COUNT (*) |

+----------+

| 33554440 |

+----------+

1 row in Set (0.00 sec)

Mysql> Explain select C1, C2, C3 from Temp Force index (IND_TEMP_C123) group by C1, C2;

+----+-------------+-------+-------+---------------+---------------+---------+------+------+------------------- -------+

| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |

+----+-------------+-------+-------+---------------+---------------+---------+------+------+------------------- -------+

| 1 | Simple | Temp | Range | NULL | ind_temp_c123 | 22 |    NULL | 3 | Using Index for Group-by |

+----+-------------+-------+-------+---------------+---------------+---------+------+------+------------------- -------+

1 row in Set (0.00 sec)

mysql> INSERT INTO C select C1, C2, Max (C3) from Temp Force index (IND_TEMP_C123) group by C1, C2;

Query OK, 2 rows affected (0.03 sec)

Records:2 duplicates:0 warnings:0

10 go to repeat after C table of data

Mysql> select * from C;

+------+------+------+

| C1 | C2 | C3 |

+------+------+------+

| 1 | 2 | 3 |

| 4 | 5 | 6 |

+------+------+------+

2 rows in Set (0.00 sec)

Mysql>

Mysql>

Mysql> SELECT * FROM temp ORDER BY C1 desc limit 10;

+------+------+------+

| C1 | C2 | C3 |

+------+------+------+

| 4 | 5 | 6 |

| 4 | 5 | 6 |

| 4 | 5 | 6 |

| 4 | 5 | 6 |

| 4 | 5 | 6 |

| 4 | 5 | 6 |

| 4 | 5 | 6 |

| 4 | 5 | 6 |

| 1 | 2 | 3 |

| 1 | 2 | 3 |

+------+------+------+

Rows in Set (0.00 sec)

Mysql> Ctrl-c--exit!

Aborted

11 Delete Table Temp

mysql> drop Table temp;

Query OK, 0 rows affected (1.59 sec)

mysql> drop table A;

Query OK, 0 rows affected (0.55 sec)

mysql> drop table B;

Query OK, 0 rows affected (0.73 sec)

Mysql> Show tables;

+----------------+

| Tables_in_test |

+----------------+

| C |

+----------------+

1 row in Set (0.00 sec)

Mysql>

• This article is from: Linux Learning Tutorial Network

Mysql: Remove heavy data with forced index