map-generator/migrations/002_add_maps_indexes.sql

-- Migration: Ajout d'index supplémentaires pour optimiser les performances
-- Date: 2025-10-12
-- Description: Index composites et fulltext pour améliorer les recherches

-- Index pour la recherche fulltext sur le nom et la description
-- Vérifier si l'index n'existe pas déjà
SET @sql = (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
     WHERE TABLE_SCHEMA = DATABASE()
     AND TABLE_NAME = 'maps'
     AND INDEX_NAME = 'idx_fulltext_name_description') = 0,
    'ALTER TABLE `maps` ADD FULLTEXT INDEX `idx_fulltext_name_description` (`name`, `description`)',
    'SELECT "Index idx_fulltext_name_description déjà existant" as message'
));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Index pour les cartes récemment modifiées (si pas déjà existant)
SET @sql = (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
     WHERE TABLE_SCHEMA = DATABASE()
     AND TABLE_NAME = 'maps'
     AND INDEX_NAME = 'idx_updated_at') = 0,
    'ALTER TABLE `maps` ADD INDEX `idx_updated_at` (`updated_at`)',
    'SELECT "Index idx_updated_at déjà existant" as message'
));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Index pour les dimensions (si pas déjà existant)
SET @sql = (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
     WHERE TABLE_SCHEMA = DATABASE()
     AND TABLE_NAME = 'maps'
     AND INDEX_NAME = 'idx_dimensions') = 0,
    'ALTER TABLE `maps` ADD INDEX `idx_dimensions` (`width`, `height`)',
    'SELECT "Index idx_dimensions déjà existant" as message'
));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;