Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to view the execution plan of the partition table in MySQL5.5". Many people will encounter this dilemma in the operation of the actual case, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

-- create a test table

Mysql > CREATE TABLE trb1 (id INT, name VARCHAR (50), purchased DATE)

-> PARTITION BY RANGE (id)

-> (

-> PARTITION p0 VALUES LESS THAN (3)

-> PARTITION p1 VALUES LESS THAN (7)

-> PARTITION p2 VALUES LESS THAN (9)

-> PARTITION p3 VALUES LESS THAN (11)

->)

Query OK, 0 rows affected (0.34 sec)

Mysql > INSERT INTO trb1 VALUES

-> (1, 'desk organiser',' 2003-10-15')

-> (2,'CD player', '1993-11-05')

-> (3,'TV set', '1996-03-10')

-> (4, 'bookcase',' 1982-01-10')

-> (5, 'exercise bike',' 2004-05-09')

-> (6, 'sofa',' 1987-06-05')

-> (7, 'popcorn maker',' 2001-11-22')

-> (8, 'aquarium',' 1992-08-04')

-> (9, 'study desk',' 1984-09-16')

-> (10, 'lava lamp',' 1998-12-25')

Query OK, 10 rows affected (0.04 sec)

Records: 10 Duplicates: 0 Warnings: 0

Use EXPLAIN PARTITION to view the execution plan of the partition table

Mysql > EXPLAIN PARTITIONS SELECT * FROM trb1

+-- +

| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |

+-- +

| | 1 | SIMPLE | trb1 | p0Magol p1Maginp2P3 | ALL | NULL | NULL | NULL | NULL | 10 |

+-- +

1 row in set (0.00 sec)

-- the EXPLAIN statement does not show the partitions used

Mysql > EXPLAIN SELECT * FROM trb1

+-- +

| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+-- +

| | 1 | SIMPLE | trb1 | ALL | NULL | NULL | NULL | NULL | 10 | |

+-- +

1 row in set (0.00 sec)

Mysql > EXPLAIN PARTITIONS SELECT * FROM trb1 WHERE id

< 5; +----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | trb1 | p0,p1 | ALL | NULL | NULL | NULL | NULL | 6 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.06 sec) mysql>

ALTER TABLE trb1 ADD PRIMARY KEY (id)

Query OK, 10 rows affected (0.18 sec)

Records: 10 Duplicates: 0 Warnings: 0

Mysql > EXPLAIN PARTITIONS SELECT * FROM trb1 WHERE id < 5

+-- +

| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |

+-- +

| | 1 | SIMPLE | trb1 | p0PowerP1 | range | PRIMARY | PRIMARY | 4 | NULL | 4 | Using where |

+-- +

1 row in set (0.00 sec)

This is the end of the content of "how to view the execution plan of the partition table by MySQL5.5". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.