partition_preserve.sql

# 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 |

partition_preserve_ruleset.sql

# 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 |

partition_ruleset_preserve.sql

# 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 |