-- Speeds up battery safety statistics across every physical charging-order shard.
|
DROP PROCEDURE IF EXISTS add_battery_statistics_indexes;
|
DELIMITER //
|
CREATE PROCEDURE add_battery_statistics_indexes()
|
BEGIN
|
DECLARE finished BOOLEAN DEFAULT FALSE;
|
DECLARE shard VARCHAR(64);
|
DECLARE existing_index INT;
|
DECLARE shards CURSOR FOR
|
SELECT table_name FROM information_schema.tables
|
WHERE table_schema = DATABASE() AND table_name REGEXP '^t_charging_order(_[0-9]+)?$';
|
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = TRUE;
|
|
OPEN shards;
|
add_indexes: LOOP
|
FETCH shards INTO shard;
|
IF finished THEN LEAVE add_indexes; END IF;
|
SELECT COUNT(*) INTO existing_index FROM information_schema.statistics
|
WHERE table_schema = DATABASE() AND table_name = shard AND index_name = 'idx_battery_statistics';
|
IF existing_index = 0 THEN
|
SET @sql = CONCAT('ALTER TABLE `', shard, '` ADD INDEX `idx_battery_statistics` '
|
'(`charging_gun_id`, `status`, `del_flag`, `create_time`)');
|
PREPARE statement FROM @sql;
|
EXECUTE statement;
|
DEALLOCATE PREPARE statement;
|
END IF;
|
END LOOP;
|
CLOSE shards;
|
END //
|
DELIMITER ;
|
CALL add_battery_statistics_indexes();
|
DROP PROCEDURE add_battery_statistics_indexes;
|
