How does MySQL5.5 view the execution plan of the partition table
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!
