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

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

Recommend

10 ways to view compressed file contents in Linux (summary)

Generally speaking, when we view the contents of ...

Detailed explanation of this pointing in JS arrow function

Arrow function is a new feature in ES6. It does n...

Implementation of MySQL scheduled database backup (full database backup)

Table of contents 1. MySQL data backup 1.1, mysql...

Detailed explanation of the hierarchical relationship problem caused by CSS positioning

Absolute, relative and fixed in position position...

Centos7.4 server installation of apache and solutions to problems encountered during the installation process

This article describes how to install Apache on a...

JavaScript to achieve text expansion and collapse effect

The implementation of expanding and collapsing li...

MySQL 5.7.33 installation process detailed illustration

Table of contents Installation package download I...

Summary of five commands to check swap space in Linux

Preface Two types of swap space can be created un...

MySQL uses binlog logs to implement data recovery

MySQL binlog is a very important log in MySQL log...

Use DIV mask to solve the problem that directly checking the checkbox with the mouse is invalid

During the front-end development process, a situat...

Why the table file size remains unchanged after deleting data in MySQL

For databases that have been running for a long t...

Detailed explanation of docker network bidirectional connection

View Docker Network docker network ls [root@maste...

Sample code for displaying a scroll bar after the HTML page is zoomed out

Here is a record of how to make a scroll bar appe...

JavaScript object built-in objects, value types and reference types explained

Table of contents Object Object Definition Iterat...

A brief discussion on Python's function knowledge

Table of contents Two major categories of functio...