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

Solution to Docker's failure to release ports

Today I encountered a very strange situation. Aft...

User needs lead to marketing-oriented design

<br />For each of our topics, the team will ...

Implementation method of Mysql tree recursive query

Preface For tree-structured data in the database,...

Use CSS to easily implement some frequently appearing weird buttons

background In the group, some students will ask r...

Using CSS3 to create header animation effects

Netease Kanyouxi official website (http://kanyoux...

vue+node+socket io realizes multi-person interaction and releases the entire process

1. Background 1. The front end uses vue + vuex + ...

Rounding operation of datetime field in MySQL

Table of contents Preface 1. Background 2. Simula...

Five ways to achieve automatic page jump in HTML

In the previous article, we introduced three comm...

A brief analysis of the game kimono memo problem

Today, after the game was restarted, I found that...

Summary of 76 Experience Points of User Experience

Classification of website experience 1. Sensory e...

MySQL scheduled full database backup

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

An Uncommon Error and Solution for SQL Server Full Backup

1. Error details Once when manually performing a ...

Docker uses Supervisor to manage process operations

A Docker container starts a single process when i...

How much do you know about JavaScript inheritance?

Table of contents Preface The relationship betwee...

Quickly solve the problem of garbled characters and jump lines in mysql exported scv files

Due to work reasons, it is often not possible to ...