Shulou(Shulou.com)06/01 Report--

The following mainly brings you whether the MySQL partition field column needs to be indexed separately. I hope that whether the MySQL partition field column needs to be indexed separately can bring you practical use, which is also the main purpose of my editing this article. All right, don't talk too much nonsense, let's just read the following.

1. Create a new table effect_new (partition by month according to creation time)

CREATE TABLE `timezone` (`id`bigint (20) NOT NULL AUTO_INCREMENT, `type`tinyint (4) NOT NULL DEFAULT '0mm, `timezone` varchar (10) DEFAULT NULL, `date`varchar (10) NOT NULL, `hour`varchar (2) DEFAULT NULL, `position` varchar (200) DEFAULT NULL, `timey` varchar (32) NOT NULL, `create_ time`datetime NOT NULL DEFAULT' 1970-01-01 00varchar 00varchar'01-01 00varchar 0012, PRIMARY KEY (`id`, `create_ time`), KEY `index_date_hour_ coun` (`date`, `hour`) `roomy`) ENGINE=InnoDB AUTO_INCREMENT=983041 DEFAULT CHARSET=utf8PARTITION 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 part of the data

INSERT INTO `timezone` (`id`, `type`, `timezone`, `date`, `hour`, `position`, `position`, `timey`, `timetime`) VALUES ('1mm,' 01mm, 'GMT+8',' 2017-07-01mm,'', 'INSERT INTO NotiCleanFullhouse FamilyRecompany 0026 colors,'',', '2017-07-02 00create_ 07pur02') INSERT INTO `timezone` (`id`, `type`, `timezone`, `date`, `hour`, `position`, `position`, `timetime`) VALUES ('2percent,' 1percent, 'GMT+8',' 2017-09-30minutes, '23cycles,' Ma5dtJub', 'EG',' 2017-10-01 0000001 create_ VALUES 00') INSERT INTO `timezone` (`id`, `type`, `timezone`, `date`, `hour`, `position`, `position`, `timetime`) VALUES ('3percent,' 1percent, 'GMT+8',' 2017-09-10 percent,'10 percent,'28 percent, 'DZ',' 2017-09-11 00 create_ create_ 20') INSERT INTO `timezone` (`id`, `type`, `timezone`, `date`, `hour`, `position`, `position`, `timetime`) VALUES ('4percent,' 1percent, 'GMT+8',' 2017-02-03,'20 percent,'32 percent, 'AD',' 2017-02-04 00 create_ create_ 00') INSERT INTO `timezone` (`id`, `type`, `timezone`, `date`, `hour`, `position`, `position`, `timetime`) VALUES ('5percent,' 0percent, 'GMT+8',' 2017-03-05percent, '2percent, NULL,' AI', '2017-03-06 02create_ create_ 00') INSERT INTO `timezone` (`id`, `type`, `timezone`, `date`, `hour`, `position`, `position`, `create_ time`) VALUES ('6mm,' 0mm, 'GMT+8',' 2017-09-23mm, '13mm,' Mlam BrandSplashmura Smer 0038mm, 'AG',' 2017-09-23 1313 create_ 0000') INSERT INTO `timezone` (`id`, `type`, `timezone`, `date`, `hour`, `position`, `position`, `timey`, `timetime`) VALUES ('7,'1, NULL, '2017-10-13,' 12-12, 'BB-Main-AppAd-0018',' AF', '2017-10-14 1212-0015) VALUES) INSERT INTO `timezone` (`id`, `type`, `timezone`, `date`, `hour`, `position`, `position`, `timetime`) VALUES ('8mm,' 0mm, 'GMT+8',' 2017-10-28mm, '2mm,' Mlure ChargeReminent create_ create_ 0040mm, 'AE',' 2017-10-290000lump 0000') INSERT INTO `timezone` (`id`, `type`, `timezone`, `date`, `hour`, `position`, `position`, `timetime`) VALUES ('9mm,' 1mm, 'GMT+8',' 2017-10-09mm, NULL, '30mm,' AI', '2017-10-1000V 09V VALUES') INSERT INTO `timezone` (`id`, `type`, `timezone`, `date`, `hour`, `position`, `position`, `timetime`) VALUES ('10mm,' 0mm, 'GMT+8',' 2017-10-05mm, '5mm,' Mlym BrandSplashure, 'LA',' 2017-10-06 05L create_ 00')

3. Analysis statement

EXPLAIN PARTITIONSselect * from effect_new_indexwhere create_time = '2017-10-14 12 0000'

The result is:

Idselect_typetablepartitionstpyepossible_keyskeykey_lenrefrowsfilteredextra1SIMPLEeffect_newp8ALLnullnullnullnull39151510Using where

4. Add the index idx_ctime to the table effect_new

5. Analyze the execution plan after adding the index

The result is:

Idselect_typetablepartitionstpyepossible_keyskeykey_lenrefrowsfilteredextra1SIMPLEeffect_newp8refidx_ctimeidx_ctime5const60760100null

6. Conclusion:

Although the table has been partitioned according to this field, this cannot be equated with an index. Divided into sections, it can only be said that the record with a certain value of the field will be in a certain partition, but it is not an index, and it will be easy to find.

Sometimes, the primary key is not equal to the partitioning column, and if the primary key wants to build a clustered index, it must include the partitioning column and make a compound primary key. So, in this case, doesn't the partition have an index based on the column? Yes, but it is not fast enough. If the partition-based column does not rank first in this composite index, it will not be fast enough. If the partition-based column is often used as the filter condition in the search statement, it is necessary to create a separate index for the partition-based column.

For the above about whether the MySQL partition field column needs to be indexed separately, do you think it is very helpful? If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.