19 lines
995 B
SQL
19 lines
995 B
SQL
-- MySQL 5.7 / 8.0; run against the IM database during an appropriate maintenance window.
|
|
-- Supports bilateral message history and candidate scans. The all-conversation
|
|
-- grouping still scans messages; review EXPLAIN with representative data.
|
|
-- This script is idempotent and does not change message rows.
|
|
SET @private_conversation_index_exists = (
|
|
SELECT COUNT(*) FROM information_schema.statistics
|
|
WHERE table_schema = DATABASE()
|
|
AND table_name = 'im_private_message'
|
|
AND index_name = 'idx_private_pair_time'
|
|
);
|
|
SET @private_conversation_index_sql = IF(
|
|
@private_conversation_index_exists = 0,
|
|
'ALTER TABLE im_private_message ADD INDEX idx_private_pair_time (send_id, recv_id, send_time, id), ALGORITHM=INPLACE, LOCK=NONE',
|
|
'SELECT ''idx_private_pair_time already exists'' AS message'
|
|
);
|
|
PREPARE private_conversation_index_stmt FROM @private_conversation_index_sql;
|
|
EXECUTE private_conversation_index_stmt;
|
|
DEALLOCATE PREPARE private_conversation_index_stmt;
|