mariadbpartitioningexplain

MariaDB does not select according to partition on partitioned table


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

Solution

  • 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