DROP PROCEDURE IF EXISTS CreateIndexIfNotExists;
 
DELIMITER //
 
CREATE PROCEDURE CreateIndexIfNotExists()
BEGIN
    DECLARE index_exists INT;
 
    -- Check if the index exists
    SELECT COUNT(1) INTO index_exists
    FROM information_schema.statistics
    WHERE table_schema = 'ocs'
      AND table_name = 'hardware'
      AND index_name = 'idx_hw_uuid';
 
    -- Create the index if it doesn't exist
    IF index_exists = 0 THEN
        SET @create_index_query = CONCAT('CREATE INDEX idx_hw_uuid ON ocs.hardware(UUID)');
        PREPARE stmt FROM @create_index_query;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        SELECT CONCAT('Index idx_hw_uuid created successfully.') AS message;
    ELSE
        SELECT CONCAT('Index idx_hw_uuid already exists.') AS message;
    END IF;
END //
DELIMITER ;
 
-- usage:
CALL CreateIndexIfNotExists();
