- cpu i5 2 cores
- RAM 8G
SHOW VARIABLES LIKE '%version%';
+-------------------------+-----------------------+
| Variable_name | Value |
+-------------------------+-----------------------+
| innodb_version | 5.7.11 |
| protocol_version | 10 |
| slave_type_conversions | |
| tls_version | TLSv1,TLSv1.1,TLSv1.2 |
| version | 5.7.11 |
| version_comment | Homebrew |
| version_compile_machine | x86_64 |
| version_compile_os | osx10.11 |
+-------------------------+-----------------------+
SELECT
PLUGIN_NAME as Name,
PLUGIN_VERSION as Version,
PLUGIN_STATUS as Status
FROM INFORMATION_SCHEMA.PLUGINS
WHERE PLUGIN_TYPE='STORAGE ENGINE';
+--------------------+---------+----------+
| Name | Version | Status |
+--------------------+---------+----------+
| ... | ... | ... |
| partition | 1.0 | ACTIVE |
+--------------------+---------+----------+When an index is created on a partitioned table, are the indexes partitioned the same way as the table?
http://forums.mysql.com/read.php?106,202130,202130#msg-202130
the answer is YES
# create test database
CREATE DATABASE `partition_test` /*!40100 DEFAULT CHARACTER SET utf8 */;
use partition_test;CREATE TABLE user_courses_partition (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`course_id` int(11) DEFAULT NULL,
`created_at` datetime NOT NULL,
`updated_at` datetime NOT NULL,
PRIMARY KEY (`id`, `user_id`),
KEY `index_ucs_on_course_id` (`course_id`),
KEY `index_ucs_on_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY HASH(user_id)
PARTITIONS 100;
CREATE TABLE courses_partition (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`school_id` int(11) NOT NULL,
`semester_id` tinyint(4) NOT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
`teacher` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`user_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`, `semester_id`),
KEY `index_cs_on_user_id_and_semester_id` (`user_id`, `semester_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY LIST (semester_id) (
PARTITION semester_17 VALUES IN (17),
PARTITION semester_16 VALUES IN (16),
PARTITION semester_15 VALUES IN (15),
PARTITION semester_14 VALUES IN (14),
PARTITION semester_13 VALUES IN (13),
PARTITION semester_12 VALUES IN (12),
PARTITION semester_11 VALUES IN (11),
PARTITION semester_10 VALUES IN (10),
PARTITION semester_9 VALUES IN (9),
PARTITION semester_8 VALUES IN (8),
PARTITION semester_7 VALUES IN (7),
PARTITION semester_6 VALUES IN (6),
PARTITION semester_5 VALUES IN (5),
PARTITION semester_4 VALUES IN (4),
PARTITION semester_3 VALUES IN (3),
PARTITION semester_2 VALUES IN (2),
PARTITION semester_1 VALUES IN (1)
);
DELIMITER //
DROP PROCEDURE IF EXISTS generate_partition_data;
CREATE PROCEDURE generate_partition_data ()
BEGIN
DECLARE course_id int;
DECLARE user_id int;
DECLARE semester_id int;
DECLARE semester_course_count int;
DELETE FROM user_courses_partition;
DELETE FROM courses_partition;
ALTER TABLE user_courses_partition AUTO_INCREMENT = 1;
ALTER TABLE courses_partition AUTO_INCREMENT = 1;
set user_id = 1;
set course_id = 1;
while user_id <= 100000
do
set semester_id = 1;
while semester_id <= 17
do
set semester_course_count = 1;
while semester_course_count <= 2
do
insert into user_courses_partition (`user_id`, `course_id`, `created_at`, `updated_at`)
values (
user_id,
course_id,
adddate('1995-01-01', (rand()*3652) mod 365),
adddate('1995-01-01', (rand()*3652) mod 365));
insert into courses_partition (`id`, `name`, `school_id`, `semester_id`, `created_at`, `updated_at`, `teacher`, `user_id`)
values (
course_id,
'name',
1,
semester_id,
adddate('1995-01-01', (rand()*3652) mod 365),
adddate('1995-01-01', (rand()*3652) mod 365),
'teacher',
user_id);
set semester_course_count = semester_course_count + 1;
set course_id = course_id + 1;
end while;
set semester_id = semester_id + 1;
end while;
set user_id = user_id + 1;
end while;
END//
DELIMITER ;
CALL generate_partition_data();
# Query OK, 1 row affected (27 min 14.92 sec)CREATE TABLE user_courses (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`course_id` int(11) DEFAULT NULL,
`created_at` datetime NOT NULL,
`updated_at` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `index_ucs_on_course_id` (`course_id`),
KEY `index_ucs_on_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE courses (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`school_id` int(11) NOT NULL,
`semester_id` tinyint(4) NOT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
`teacher` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`user_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_cs_on_user_id_and_semester_id` (`user_id`, `semester_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DELIMITER //
DROP PROCEDURE IF EXISTS generate_common_data;
CREATE PROCEDURE generate_common_data ()
BEGIN
DECLARE course_id int;
DECLARE user_id int;
DECLARE semester_id int;
DECLARE semester_course_count int;
DELETE FROM user_courses;
DELETE FROM courses;
ALTER TABLE user_courses AUTO_INCREMENT = 1;
ALTER TABLE courses AUTO_INCREMENT = 1;
set user_id = 1;
set course_id = 1;
while user_id <= 100000
do
set semester_id = 1;
while semester_id <= 17
do
set semester_course_count = 1;
while semester_course_count <= 2
do
insert into user_courses (`user_id`, `course_id`, `created_at`, `updated_at`)
values (
user_id,
course_id,
adddate('1995-01-01', (rand()*3652) mod 365),
adddate('1995-01-01', (rand()*3652) mod 365));
insert into courses (`id`, `name`, `school_id`, `semester_id`, `created_at`, `updated_at`, `teacher`, `user_id`)
values (
course_id,
'name',
1,
semester_id,
adddate('1995-01-01', (rand()*3652) mod 365),
adddate('1995-01-01', (rand()*3652) mod 365),
'teacher',
user_id);
set semester_course_count = semester_course_count + 1;
set course_id = course_id + 1;
end while;
set semester_id = semester_id + 1;
end while;
set user_id = user_id + 1;
end while;
END//
DELIMITER ;
CALL generate_common_data();
# Query OK, 1 row affected (26 min 38.64 sec)