Skip to content

Instantly share code, notes, and snippets.

@peppy
Created August 23, 2022 06:48
Show Gist options
  • Save peppy/8851f98d0fb783ff29043f408e6a3923 to your computer and use it in GitHub Desktop.
Save peppy/8851f98d0fb783ff29043f408e6a3923 to your computer and use it in GitHub Desktop.
# noinspection SqlResolveForFile
-- Partitioned by (preserve)
DROP TABLE IF EXISTS `partitioned_solo_scores`;
-- existing structure on production
CREATE TABLE `partitioned_solo_scores` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`user_id` int unsigned NOT NULL,
`beatmap_id` mediumint unsigned NOT NULL,
`ruleset_id` smallint unsigned NOT NULL,
`data` json NOT NULL,
`has_replay` tinyint(1) DEFAULT '0',
`preserve` tinyint(1) DEFAULT '0',
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `solo_scores_preserve_index` (`preserve`),
KEY `user_ruleset_id_index` (`user_id`,`ruleset_id`,`id` DESC),
KEY `beatmap_id` (`beatmap_id`)
) ENGINE=InnoDB AUTO_INCREMENT=46153978 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
ALTER TABLE `partitioned_solo_scores`
DROP PRIMARY KEY,
ADD PRIMARY KEY (`id`, `ruleset_id`, `preserve`),
-- not required, always optimised via partition pruning.
DROP INDEX `solo_scores_preserve_index`;
ALTER TABLE `partitioned_solo_scores`
PARTITION BY LIST COLUMNS (`preserve`)
(
-- preserve false
PARTITION p0_0 VALUES IN (0),
-- preserve true
PARTITION p1_0 VALUES IN (1)
);
EXPLAIN SELECT * FROM partitioned_solo_scores WHERE user_id = 2 AND ruleset_id = 0;
-- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-- |----|-------------|-------------------------|------------|------|-----------------------|-----------------------|---------|-------------|------|----------|--------|
-- | 1 | SIMPLE | partitioned_solo_scores | p0_0,p1_0 | ref | user_ruleset_id_index | user_ruleset_id_index | 6 | const,const | 1 | 100.0 | <null> |
EXPLAIN SELECT * FROM partitioned_solo_scores WHERE user_id = 2 AND ruleset_id = 0 and preserve = 1;
-- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-- |----|-------------|-------------------------|------------|------|-----------------------|-----------------------|---------|-------------|------|----------|-----------------------|
-- | 1 | SIMPLE | partitioned_solo_scores | p1_0 | ref | user_ruleset_id_index | user_ruleset_id_index | 6 | const,const | 1 | 100.0 | Using index condition |
EXPLAIN SELECT * FROM partitioned_solo_scores WHERE preserve = 1;
-- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-- |----|-------------|-------------------------|------------|------|---------------|--------|---------|--------|------|----------|-------------|
-- | 1 | SIMPLE | partitioned_solo_scores | p1_0 | ALL | <null> | <null> | <null> | <null> | 1 | 100.0 | Using where |
EXPLAIN SELECT * FROM partitioned_solo_scores WHERE ruleset_id = 0 and preserve = 1;
-- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-- |----|-------------|-------------------------|------------|------|---------------|--------|---------|--------|------|----------|-------------|
-- | 1 | SIMPLE | partitioned_solo_scores | p1_0 | ALL | <null> | <null> | <null> | <null> | 1 | 100.0 | Using where |
# noinspection SqlResolveForFile
-- Partitioned by (preserve, ruleset_id)
DROP TABLE IF EXISTS `partitioned_solo_scores`;
-- existing structure on production
CREATE TABLE `partitioned_solo_scores` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`user_id` int unsigned NOT NULL,
`beatmap_id` mediumint unsigned NOT NULL,
`ruleset_id` smallint unsigned NOT NULL,
`data` json NOT NULL,
`has_replay` tinyint(1) DEFAULT '0',
`preserve` tinyint(1) DEFAULT '0',
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `solo_scores_preserve_index` (`preserve`),
KEY `user_ruleset_id_index` (`user_id`,`ruleset_id`,`id` DESC),
KEY `beatmap_id` (`beatmap_id`)
) ENGINE=InnoDB AUTO_INCREMENT=46153978 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
ALTER TABLE `partitioned_solo_scores`
DROP PRIMARY KEY,
ADD PRIMARY KEY (`id`, `ruleset_id`, `preserve`),
-- not required, always optimised via partition pruning.
DROP INDEX `solo_scores_preserve_index`;
ALTER TABLE `partitioned_solo_scores`
PARTITION BY LIST COLUMNS (`preserve`, `ruleset_id`)
(
-- preserve false
PARTITION p0_0 VALUES IN ((0, 0)),
PARTITION p0_1 VALUES IN ((0, 1)),
PARTITION p0_2 VALUES IN ((0, 2)),
PARTITION p0_3 VALUES IN ((0, 3)),
-- preserve true
PARTITION p1_0 VALUES IN ((1, 0)),
PARTITION p1_1 VALUES IN ((1, 1)),
PARTITION p1_2 VALUES IN ((1, 2)),
PARTITION p1_3 VALUES IN ((1, 3))
);
EXPLAIN SELECT * FROM partitioned_solo_scores WHERE user_id = 2 AND ruleset_id = 0;
-- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-- |----|-------------|-------------------------|-----------------------------------------|------|-----------------------|-----------------------|---------|-------------|------|----------|--------|
-- | 1 | SIMPLE | partitioned_solo_scores | p0_0,p0_1,p0_2,p0_3,p1_0,p1_1,p1_2,p1_3 | ref | user_ruleset_id_index | user_ruleset_id_index | 6 | const,const | 1 | 100.0 | <null> |
EXPLAIN SELECT * FROM partitioned_solo_scores WHERE user_id = 2 AND ruleset_id = 0 and preserve = 1;
-- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-- |----|-------------|-------------------------|------------|------|-----------------------|-----------------------|---------|-------------|------|----------|-----------------------|
-- | 1 | SIMPLE | partitioned_solo_scores | p1_0 | ref | user_ruleset_id_index | user_ruleset_id_index | 6 | const,const | 1 | 100.0 | Using index condition |
EXPLAIN SELECT * FROM partitioned_solo_scores WHERE preserve = 1;
-- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-- |----|-------------|-------------------------|---------------------|------|---------------|--------|---------|--------|------|----------|-------------|
-- | 1 | SIMPLE | partitioned_solo_scores | p1_0,p1_1,p1_2,p1_3 | ALL | <null> | <null> | <null> | <null> | 1 | 100.0 | Using where |
EXPLAIN SELECT * FROM partitioned_solo_scores WHERE ruleset_id = 0 and preserve = 1;
-- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-- |----|-------------|-------------------------|------------|------|---------------|--------|---------|--------|------|----------|-------------|
-- | 1 | SIMPLE | partitioned_solo_scores | p1_0 | ALL | <null> | <null> | <null> | <null> | 1 | 100.0 | Using where |
# noinspection SqlResolveForFile
-- Partitioned by (ruleset_id, preserve)
DROP TABLE IF EXISTS `partitioned_solo_scores`;
-- existing structure on production
CREATE TABLE `partitioned_solo_scores` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`user_id` int unsigned NOT NULL,
`beatmap_id` mediumint unsigned NOT NULL,
`ruleset_id` smallint unsigned NOT NULL,
`data` json NOT NULL,
`has_replay` tinyint(1) DEFAULT '0',
`preserve` tinyint(1) DEFAULT '0',
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `solo_scores_preserve_index` (`preserve`),
KEY `user_ruleset_id_index` (`user_id`,`ruleset_id`,`id` DESC),
KEY `beatmap_id` (`beatmap_id`)
) ENGINE=InnoDB AUTO_INCREMENT=46153978 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
ALTER TABLE `partitioned_solo_scores`
DROP PRIMARY KEY,
ADD PRIMARY KEY (`id`, `ruleset_id`, `preserve`),
-- shouldn't be required if queries either include `ruleset_id` condition,
-- OR select partitions (mysql can't optimise this).
DROP INDEX `solo_scores_preserve_index`,
-- ruleset not required in index as it is always available via partition pruning.
DROP INDEX `user_ruleset_id_index`,
ADD INDEX `user_ruleset_id_index` (`user_id`, `id` DESC);
ALTER TABLE `partitioned_solo_scores`
PARTITION BY LIST COLUMNS (`ruleset_id`, `preserve`)
(
-- preserve false
PARTITION p0_0 VALUES IN ((0, 0)),
PARTITION p0_1 VALUES IN ((1, 0)),
PARTITION p0_2 VALUES IN ((2, 0)),
PARTITION p0_3 VALUES IN ((3, 0)),
-- preserve true
PARTITION p1_0 VALUES IN ((0, 1)),
PARTITION p1_1 VALUES IN ((1, 1)),
PARTITION p1_2 VALUES IN ((2, 1)),
PARTITION p1_3 VALUES IN ((3, 1))
);
EXPLAIN SELECT * FROM partitioned_solo_scores WHERE user_id = 2 AND ruleset_id = 0;
-- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-- |----|-------------|-------------------------|------------|------|-----------------------|-----------------------|---------|-------|------|----------|-----------------------|
-- | 1 | SIMPLE | partitioned_solo_scores | p0_0,p1_0 | ref | user_ruleset_id_index | user_ruleset_id_index | 4 | const | 1 | 100.0 | Using index condition |
EXPLAIN SELECT * FROM partitioned_solo_scores WHERE user_id = 2 AND ruleset_id = 0 and preserve = 1;
-- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-- |----|-------------|-------------------------|------------|------|-----------------------|-----------------------|---------|-------|------|----------|-----------------------|
-- | 1 | SIMPLE | partitioned_solo_scores | p1_0 | ref | user_ruleset_id_index | user_ruleset_id_index | 4 | const | 1 | 100.0 | Using index condition |
EXPLAIN SELECT * FROM partitioned_solo_scores WHERE preserve = 1;
-- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-- |----|-------------|-------------------------|-----------------------------------------|------|---------------|--------|---------|--------|------|----------|-------------|
-- | 1 | SIMPLE | partitioned_solo_scores | p0_0,p0_1,p0_2,p0_3,p1_0,p1_1,p1_2,p1_3 | ALL | <null> | <null> | <null> | <null> | 1 | 100.0 | Using where |
EXPLAIN SELECT * FROM partitioned_solo_scores WHERE ruleset_id = 0 and preserve = 1;
-- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-- |----|-------------|-------------------------|------------|------|---------------|--------|---------|--------|------|----------|-------------|
-- | 1 | SIMPLE | partitioned_solo_scores | p1_0 | ALL | <null> | <null> | <null> | <null> | 1 | 100.0 | Using where |
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment