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

Everyone knows that the partition field must be part of the primary key. So after building the composite primary key, is it necessary to add a separate index for the partition field? Is it effective? This article mainly introduces to you the relevant information on whether it is necessary to build a separate index for the MySQL partition field column. The article is verified through examples and has certain reference learning value for everyone's understanding and learning. Friends who need it follow the small Let’s learn together.

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_coun` (`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 Partial data 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. Analysis statement

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

The result is:

##idselect_typetablepartitionstpyepossible_keyskeykey_lenrefrowsfilteredextra##14. Add index idx_ctime to table effect_new

SIMPLE	effect_new	p8	ALL	null	null	null	null	391515	10	Using where

5. Analyze the execution after adding the index Plan

results in:

##idselect_typetablepartitionstpyepossible_keyskeykey_lenref rowsfilteredextra1SIMPLEeffect_newp8refidx_ctimeidx_ctime5const60760100null6. Conclusion:

Although the table has been partitioned based on this field, this cannot be equivalent to an index. After partitioning, it can only be said that records with a certain value in the field will be in a certain partition, but it is not an index, and it will be difficult to find.

Sometimes, the primary key is not equal to the partitioning basis column. At this time, if the primary key wants to build a clustered index, then the partitioning basis column must be included to make it a composite primary key. So, in this case, doesn’t the partitioning column have an index? Yes, but it is not fast enough. If the partitioning basis column is not ranked first in this composite index, it will not be fast enough. If the partitioning basis column is often used as a filter condition in the search statement, it is necessary to add an additional column to the partitioning basis column. Create a separate index.

Related recommendations:

MYSQL’s partition field must be included in the primary key field_MySQL

MySQL’s different storage engines and different partitions The impact of fields on queries_MySQL

MySQL partition table partition online modification of partition fields_MySQL

The above is the detailed content of Is it necessary to create a separate index for the MySQL partition field column?. For more information, please follow other related articles on the PHP Chinese website!