To avoid expensive full-partition scans when querying by assigneeId (which does not include the process_instance_id partition key), use a dedicated, non-partitioned index table: se_user_task_index.
How it works
- Purpose: Stores only active/pending tasks to keep the table size small and query performance high.
- Data Redundancy: Redundantly stores common query fields (e.g.,
process_definition_type, domain_code, title) to avoid JOINs back to the partitioned main tables. - Lifecycle Management:
- Insert: When
TaskAssignee.insert() is called, insert a record into the index table. - Delete: When a task is completed, canceled, or the assignee is deleted, remove the corresponding record from the index table.
- Update: When task metadata (title, priority, etc.) changes, update the index table.
Schema Implementation
PostgreSQL (with Partial Indexing)
CREATE TABLE se_user_task_index (
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
tenant_id varchar(64),
assignee_id varchar(255) NOT NULL,
assignee_type varchar(128) NOT NULL DEFAULT 'user',
task_instance_id bigint NOT NULL,
process_instance_id bigint NOT NULL,
process_definition_type varchar(255),
domain_code varchar(64),
extra jsonb,
task_status varchar(64) NOT NULL,
task_gmt_modified timestamp(6),
title varchar(255),
priority int DEFAULT 500,
CONSTRAINT uk_user_task_idx UNIQUE (tenant_id, assignee_id, task_instance_id)
);
-- Optimized partial index for pending tasks
CREATE INDEX idx_user_task_pending ON se_user_task_index
(tenant_id, assignee_id, assignee_type, task_status)
WHERE task_status = 'pending';
MySQL
CREATE TABLE se_user_task_index (
id bigint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
tenant_id varchar(64),
assignee_id varchar(255) NOT NULL,
assignee_type varchar(128) NOT NULL DEFAULT 'user',
task_instance_id bigint NOT NULL,
process_instance_id bigint NOT NULL,
process_definition_type varchar(255),
domain_code varchar(64),
extra json,
task_status varchar(64) NOT NULL,
task_gmt_modified datetime(6),
title varchar(255),
priority int DEFAULT 500,
UNIQUE KEY uk_user_task_idx (tenant_id, assignee_id, task_instance_id)
);
-- MySQL uses standard composite index as it lacks partial indexes
CREATE INDEX idx_user_task_pending ON se_user_task_index
(tenant_id, assignee_id, assignee_type, task_status);
-- PostgreSQL Partial Index Example
CREATE INDEX idx_user_task_pending ON se_user_task_index
(tenant_id, assignee_id, assignee_type, task_status)
WHERE task_status = 'pending';