Is it necessary to create a separate index for the MySQL partition field column?

Preface

Everyone knows that the partition field must be part of the primary key. So after creating a composite primary key, do you need to add a separate index for the partition field? Is there any effect? Let’s verify it. Without further ado, let’s take a look at the detailed introduction.

1. Create a new table effect_new (partitioned by month based on creation time)

CREATE TABLE `effect_new` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `type` tinyint(4) NOT NULL DEFAULT '0',
 `timezone` varchar(10) DEFAULT NULL,
 `date` varchar(10) NOT NULL,
 `hour` varchar(2) DEFAULT NULL,
 `position` varchar(200) DEFAULT NULL,
 `country` varchar(32) NOT NULL,
 `create_time` datetime NOT NULL DEFAULT '1970-01-01 00:00:00',
 PRIMARY KEY (`id`,`create_time`),
 KEY `index_date_hour_country` (`date`,`hour`,`country`)
) ENGINE=InnoDB AUTO_INCREMENT=983041 DEFAULT CHARSET=utf8
PARTITION BY RANGE (TO_DAYS (`create_time`))
(PARTITION p0 VALUES LESS THAN (736754) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (736785) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (736815) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (736846) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (736876) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (736907) ENGINE = InnoDB,
 PARTITION p6 VALUES LESS THAN (736938) ENGINE = InnoDB,
 PARTITION p7 VALUES LESS THAN (736968) ENGINE = InnoDB,
 PARTITION p8 VALUES LESS THAN (736999) ENGINE = InnoDB,
 PARTITION p9 VALUES LESS THAN (737029) ENGINE = InnoDB,
 PARTITION p10 VALUES LESS THAN (737060) ENGINE = InnoDB);

2. Insert some data,

INSERT INTO `effect_new` (`id`, `type`, `timezone`, `date`, `hour`, `position`, `country`, `create_time`) VALUES ('1', '0', 'GMT+8', '2017-07-01', '', 'M-NotiCleanFull-FamilyRecom-0026', '', '2017-07-02 00:07:02');
INSERT INTO `effect_new` (`id`, `type`, `timezone`, `date`, `hour`, `position`, `country`, `create_time`) VALUES ('2', '1', 'GMT+8', '2017-09-30', '23', 'Ma5dtJub', 'EG', '2017-10-01 00:00:00');
INSERT INTO `effect_new` (`id`, `type`, `timezone`, `date`, `hour`, `position`, `country`, `create_time`) VALUES ('3', '1', 'GMT+8', '2017-09-10', '10', '28', 'DZ', '2017-09-11 00:08:20');
INSERT INTO `effect_new` (`id`, `type`, `timezone`, `date`, `hour`, `position`, `country`, `create_time`) VALUES ('4', '1', 'GMT+8', '2017-02-03', '20', '32', 'AD', '2017-02-04 00:00:00');
INSERT INTO `effect_new` (`id`, `type`, `timezone`, `date`, `hour`, `position`, `country`, `create_time`) VALUES ('5', '0', 'GMT+8', '2017-03-05', '2', NULL, 'AI', '2017-03-06 02:10:00');
INSERT INTO `effect_new` (`id`, `type`, `timezone`, `date`, `hour`, `position`, `country`, `create_time`) VALUES ('6', '0', 'GMT+8', '2017-09-23', '13', 'M-BrandSplash-S-0038', 'AG', '2017-09-23 13:00:00');
INSERT INTO `effect_new` (`id`, `type`, `timezone`, `date`, `hour`, `position`, `country`, `create_time`) VALUES ('7', '1', NULL, '2017-10-13', '12', 'BB-Main-AppAd-0018', 'AF', '2017-10-14 12:00:00');
INSERT INTO `effect_new` (`id`, `type`, `timezone`, `date`, `hour`, `position`, `country`, `create_time`) VALUES ('8', '0', 'GMT+8', '2017-10-28', '2', 'M-ChargeReminder-S-0040', 'AE', '2017-10-29 00:00:00');
INSERT INTO `effect_new` (`id`, `type`, `timezone`, `date`, `hour`, `position`, `country`, `create_time`) VALUES ('9', '1', 'GMT+8', '2017-10-09', NULL, '30', 'AI', '2017-10-10 00:09:00');
INSERT INTO `effect_new` (`id`, `type`, `timezone`, `date`, `hour`, `position`, `country`, `create_time`) VALUES ('10', '0', 'GMT+8', '2017-10-05', '5', ' M-BrandSplash', 'LA', '2017-10-06 05:10:00');

3. Analyze statements

EXPLAIN PARTITIONS
select * from effect_new_index
where create_time = '2017-10-14 12:00:00'

The result is:

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Filter by	extra
1	SIMPLE	effect_new	p8	ALL	null	null	null	null	391515	10	Using where

4. Add index idx_ctime to table effect_new

5. Analyze the execution plan after adding the index

The result is:

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Filter by	extra
1	SIMPLE	effect_new	p8	ref	idx_ctime	idx_ctime	5	const	60760	100	null

6. Conclusion:

Although the table is partitioned by this field, this is not equivalent to an index. After partitioning, we can only say that the records with a certain value in the field will be in a certain partition, but it is not an index and it takes a while to find them.

Sometimes, the primary key is not equal to the partitioning column. If you want to create a clustered index on the primary key, you must include the partitioning column to make it a composite primary key. So, in this case, doesn't the partition basis column have an index? Yes, but it is not fast enough. If the partitioning column is not ranked first in this composite index, it is not fast enough. If the partitioning column is often used as a filtering condition in search statements, it is necessary to create an additional index for the partitioning column.

Summarize

The above is the full content of this article. There are still many shortcomings in this article. I hope that the content of this article will have certain reference learning value for everyone’s study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.

You may also be interested in:

MySQL advanced features - detailed explanation of the concept and mechanism of data table partitioning
In-depth explanation of MySql table, database, sharding and partitioning knowledge
Introduction to MySql table, database, sharding and partitioning knowledge points
Specific implementation methods of MySQL table sharding and partitioning
MySQL partitioning practice through Navicat
Correct use of MySQL partition tables
Mysql optimization Zabbix partition optimization
MySQL database table partitioning considerations [recommended]
A Brief Analysis of MySQL Data Table Partition Technology
MySQL data table partitioning strategy and advantages and disadvantages analysis

<<: VMware WorkStation 14 pro installation Ubuntu 17.04 tutorial

>>: Vue+elementUI component recursively implements foldable dynamic rendering multi-level sidebar navigation

Is it necessary to create a separate index for the MySQL partition field column?

Measured image HTTP request

Steps to transplant the new kernel to the Linux system

Vue+echart realizes double column chart

Problems encountered when updating the auto-increment primary key id in Mysql

Detailed explanation of configuring keepalived log to another path in centos7

Detailed examples of Linux disk device and LVM management commands

Navigation Design and Information Architecture

Example analysis of mysql variable usage [system variables, user variables]

Three ways to avoid duplicate insertion of data in MySql

CSS3 solution to the problem of freezing on mobile devices (animation performance optimization)

Recommend

Django2.* + Mysql5.7 development environment integration tutorial diagram

The latest Linux installation process of tomcat8

Detailed explanation of the benefits of PNG in various network image formats

Logrotate implements Catalina.out log rotation every two hours

Detailed explanation of data sharing between Vue components

Detailed explanation of Mysql logical architecture

Six inheritance methods in JS and their advantages and disadvantages

Vue implements drag and drop or click to upload pictures

Summary of twelve methods of Vue value transfer

Explanation of Truncate Table usage

Detailed installation and configuration tutorial of MySQL 5.7 under Win10

Solve the problem of not being able to enter breakpoints when using GDB in Docker

Pure CSS code to achieve flow and dynamic line effects

Use of marker tags in CSS list model

js to achieve the pop-up effect