-- AI module performance indexes
|
-- MySQL repeatable script: create each index only when the same index name does not already exist.
|
-- If an existing index uses the same name but different columns, compare it manually before changing it.
|
|
set @db_name = database();
|
|
set @sql = if (
|
exists (
|
select 1
|
from information_schema.statistics
|
where table_schema = @db_name
|
and table_name = 'sys_ai_chat_session'
|
and index_name = 'idx_sys_ai_chat_session_user_prompt_active'
|
),
|
'select ''skip idx_sys_ai_chat_session_user_prompt_active''',
|
'create index idx_sys_ai_chat_session_user_prompt_active on sys_ai_chat_session (tenant_id, user_id, prompt_code, deleted, status, pinned, last_message_time, id)'
|
);
|
prepare stmt from @sql;
|
execute stmt;
|
deallocate prepare stmt;
|
|
set @sql = if (
|
exists (
|
select 1
|
from information_schema.statistics
|
where table_schema = @db_name
|
and table_name = 'sys_ai_chat_message'
|
and index_name = 'idx_sys_ai_chat_message_session_active_seq'
|
),
|
'select ''skip idx_sys_ai_chat_message_session_active_seq''',
|
'create index idx_sys_ai_chat_message_session_active_seq on sys_ai_chat_message (session_id, deleted, seq_no, id)'
|
);
|
prepare stmt from @sql;
|
execute stmt;
|
deallocate prepare stmt;
|
|
set @sql = if (
|
exists (
|
select 1
|
from information_schema.statistics
|
where table_schema = @db_name
|
and table_name = 'sys_ai_call_log'
|
and index_name = 'idx_sys_ai_call_log_tenant_status'
|
),
|
'select ''skip idx_sys_ai_call_log_tenant_status''',
|
'create index idx_sys_ai_call_log_tenant_status on sys_ai_call_log (tenant_id, deleted, status, id)'
|
);
|
prepare stmt from @sql;
|
execute stmt;
|
deallocate prepare stmt;
|
|
set @sql = if (
|
exists (
|
select 1
|
from information_schema.statistics
|
where table_schema = @db_name
|
and table_name = 'sys_ai_mcp_call_log'
|
and index_name = 'idx_sys_ai_mcp_call_log_call'
|
),
|
'select ''skip idx_sys_ai_mcp_call_log_call''',
|
'create index idx_sys_ai_mcp_call_log_call on sys_ai_mcp_call_log (call_log_id, tenant_id, id)'
|
);
|
prepare stmt from @sql;
|
execute stmt;
|
deallocate prepare stmt;
|
|
set @sql = if (
|
exists (
|
select 1
|
from information_schema.statistics
|
where table_schema = @db_name
|
and table_name = 'sys_ai_mcp_call_log'
|
and index_name = 'idx_sys_ai_mcp_call_log_tenant_status'
|
),
|
'select ''skip idx_sys_ai_mcp_call_log_tenant_status''',
|
'create index idx_sys_ai_mcp_call_log_tenant_status on sys_ai_mcp_call_log (tenant_id, status, id)'
|
);
|
prepare stmt from @sql;
|
execute stmt;
|
deallocate prepare stmt;
|
|
-- Deployment note:
|
-- Clear legacy AI cache keys before the new version takes traffic to avoid stale payload compatibility issues.
|
