I have this paradigm (Laravel app): When you edit and entity in my app, I do not edit the record on database. Instead I duplicate the entity as a new one. And every editable entity in database has a row connected to it in "change_metadata_partitioned" table in the same database. Every row in "change_metadata_partitioned" table has a column which says "obsolete". If Obsolete is 1 then this row belongs to an older version of the edited entity. As you would expect this created a lot of unused data. Because When I fetch the effective version of any data from database I look for the rows that has "obsolete=0". That's why I created a partition on "change_metadata_partitioned" table as follows:
Create Table: CREATE TABLE `change_metadata_partitioned` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`table_name` varchar(255) NOT NULL,
`changeable_id` bigint(20) unsigned NOT NULL,
`created_by` bigint(20) unsigned DEFAULT NULL,
`deleted` int(11) NOT NULL DEFAULT 0,
`confirmed_at` datetime DEFAULT NULL,
`confirmed_by` bigint(20) unsigned DEFAULT NULL,
`rejected_at` varchar(255) DEFAULT NULL,
`rejected_by` bigint(20) unsigned DEFAULT NULL,
`deleted_by` bigint(20) unsigned DEFAULT NULL,
`obsolete` int(11) NOT NULL DEFAULT 0,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
`deleted_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`,`obsolete`)
) ENGINE=InnoDB AUTO_INCREMENT=5483 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
PARTITION BY LIST (`obsolete`)
(PARTITION `dObsolete` VALUES IN (1) ENGINE = InnoDB,
PARTITION `dNotObsolete` VALUES IN (0) ENGINE = InnoDB)
According to this partition when I select from this table with a "WHERE obsolete=?" clasuse it should search only on one partition. But see what "EXPLAIN SELECT * FROM change_metadata_partitioned" prints out:
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: change_metadata_partitioned
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1765
Extra: Using where
As you can see, there is no partitioning here. What can be cause of this?
MariaDB version: Ver 15.1 Distrib 10.4.28-MariaDB, for Win64 (AMD64)
EDIT:
This is the result of the query "explain select * from change_metadata_partitioned where obsolete=1"
id: 1
select_type: SIMPLE
table: change_metadata_partitioned
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1765
Extra: Using where
EXPLAIN
doesn't list partitions by default.
Instead of use EXPLAIN PARTITIONS
command:
CREATE TABLE t1 (a TINYINT NOT NULL) PARTITION BY LIST(a)
(PARTITION `part1` VALUES IN (1), PARTITION `part2` VALUES IN (2));
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a=1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: part1
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2
Extra: Using where