MySQL partitioning and temporary tables

Asked 1 month ago
Viewed 6 times

A large table (~10.5M rows) has been causing issues lately. I previously modified my application to use temporary tables for faster selects, but was still having issues due to UPDATE statements. Today I implemented partitions so that the writes happen more quickly, but now my temporary tables error. Its purpose is to group events, placing the first event ID of a set in the EVENT_ID column. Example: writing 4 events beginning at 1000 would result in events 1000, 1001, 1002, 1003, all with an EVENT_ID of 1000. I have tried to do away with the UPDATE statements, but that would require too much refactoring, so it is not an option. Here is the table definition:

CREATE TABLE `all_events` (
  `ID` bigint NOT NULL AUTO_INCREMENT,
  `EVENT_ID` bigint unsigned DEFAULT NULL,
  `LAST_UPDATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `EMPLOYEE_ID` int unsigned NOT NULL,
  `QUANTITY` float unsigned NOT NULL,
  `OPERATORS` float unsigned NOT NULL DEFAULT '0',
  `SECSEARNED` decimal(10,2) unsigned NOT NULL DEFAULT '0.00' COMMENT 'for all parts in QUANTITY',
  `SECSBURNED` decimal(10,2) unsigned NOT NULL DEFAULT '0.00',
  `YR` smallint unsigned NOT NULL DEFAULT (year(curdate())),
  PRIMARY KEY (`ID`,`YR`),
  KEY `LAST_UPDATE` (`LAST_UPDATE`),
  KEY `EMPLOYEE_ID` (`EMPLOYEE_ID`),
  KEY `EVENT_ID` (`EVENT_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=17464583 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
/*!50100 PARTITION BY RANGE (`YR`)
(PARTITION p2015 VALUES LESS THAN (2016) ENGINE = InnoDB,
 PARTITION p2016 VALUES LESS THAN (2017) ENGINE = InnoDB,
 PARTITION p2017 VALUES LESS THAN (2018) ENGINE = InnoDB,
 PARTITION p2018 VALUES LESS THAN (2019) ENGINE = InnoDB,
 PARTITION p2019 VALUES LESS THAN (2020) ENGINE = InnoDB,
 PARTITION p2020 VALUES LESS THAN (2021) ENGINE = InnoDB,
 PARTITION p2021 VALUES LESS THAN (2022) ENGINE = InnoDB,
 PARTITION p2022 VALUES LESS THAN (2023) ENGINE = InnoDB,
 PARTITION p2023 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

Now in my application when running a report the statement:

CREATE TEMPORARY TABLE IF NOT EXISTS ape ENGINE=MEMORY AS
SELECT * FROM all_events
WHERE LAST_UPDATE BETWEEN '2022-05-01 00:00:00' AND CURRENT_TIMESTAMP()

Produces the error: 'Specified storage engine' is not supported for default value expressions.

Is there a way to still use temporary tables with ENGINE=MEMORY, or is there another high performance engine I can use? InnoDB is the only engine my tables can be in due to the MySQL implementation.

asked 1 month ago

Correct Answer

I would suppose to read the following link from dev.MySQL.com

You cannot use CREATE TEMPORARY TABLE ... LIKE to create an empty table based on the definition of a table that resides in the mysql tablespace, InnoDB system tablespace (innodb_system), or a general tablespace. The tablespace definition for such a table includes a TABLESPACE attribute that defines the tablespace where the table resides, and the aforementioned tablespaces do not support temporary tables. To create a temporary table based on the definition of such a table, use this syntax instead:

CREATE TEMPORARY TABLE new_tbl SELECT * FROM orig_tbl LIMIT 0;

So it seems the correct syntax for your case will be:

CREATE TEMPORARY TABLE ape
SELECT * FROM all_events
WHERE... 
answered 1 month ago