为所有WordPress数据库添加post_title索引

#!/bin/bash/重启mysql
# add_wp_indexes.sh # 为所有WordPress站点添加3个核心索引
# idx_type_status_date # 解决分类/标签页ORDER BY慢查询
# idx_term_taxonomy # 解决分类关联查询慢
# idx_post_title # 解决文章标题搜索慢（已包含检查逻辑）

MYSQL_USER="root"
MYSQL_PASS="你的密码"

# 获取所有WordPress数据库
DATABASES=$(mysql -u"$MYSQL_USER" -p"$MYSQL_PASS" -e "SHOW DATABASES;" | grep -Ev '(Database|information_schema|mysql|performance_schema|sys)')

echo "开始为WordPress数据库添加索引..."

for DB in $DATABASES; do
    echo "处理数据库: $DB"
    
    # 检查是否是WordPress数据库
    TABLE_EXISTS=$(mysql -u"$MYSQL_USER" -p"$MYSQL_PASS" -e "USE \`$DB\`; SHOW TABLES LIKE 'wp_posts';" 2>/dev/null | wc -l)
    
    if [ "$TABLE_EXISTS" -eq 2 ]; then
        echo "  ✓ 是WordPress站点"
        
        # 添加核心索引
        echo "  添加索引 idx_type_status_date..."
        mysql -u"$MYSQL_USER" -p"$MYSQL_PASS" -e "USE \`$DB\`; 
        ALTER TABLE wp_posts ADD INDEX idx_type_status_date (post_type, post_status, post_date DESC);" 2>/dev/null
        
        echo "  添加索引 idx_term_taxonomy..."
        mysql -u"$MYSQL_USER" -p"$MYSQL_PASS" -e "USE \`$DB\`; 
        ALTER TABLE wp_term_relationships ADD INDEX idx_term_taxonomy (term_taxonomy_id, object_id);" 2>/dev/null
        
        echo "  检查并添加 idx_post_title..."
        mysql -u"$MYSQL_USER" -p"$MYSQL_PASS" -e "USE \`$DB\`; 
        SET @exist := (SELECT COUNT(*) FROM information_schema.STATISTICS 
                      WHERE TABLE_SCHEMA = '$DB' 
                      AND TABLE_NAME = 'wp_posts' 
                      AND INDEX_NAME = 'idx_post_title');
        SET @sqlstmt := IF(@exist = 0, 'ALTER TABLE wp_posts ADD INDEX idx_post_title (post_title(191))', 'SELECT \"索引已存在\"');
        PREPARE stmt FROM @sqlstmt;
        EXECUTE stmt;" 2>/dev/null
        
        echo "  ✓ $DB 索引添加完成"
    else
        echo "  - 不是WordPress数据库，跳过"
    fi
    echo
done

echo "所有数据库索引添加完成！"