Snippet Name: Create_Index_Script_Aurora

Tags:

Created Date: 2025-06-16 13:12:44
Last Modified Date: 2025-05-31 10:32:09

Dependencies:

DBA Notes:

Sample Output:

Title: Create Index Script Aurora
Author: user
In MySQL Aurora, can you help me write create index if not exists scritps for all indexes, in all databases, that have a name starting with 'IX_LI_%'

Author: ChatGPT
Yes! In MySQL (including Aurora), there isn't a direct `CREATE INDEX IF NOT EXISTS` command like in PostgreSQL, but you can achieve this by querying the `information_schema.STATISTICS` table to check if the index already exists before creating it.

Here's a script that generates `CREATE INDEX` statements for all indexes that start with `'IX_LI_%'` across all databases:

```sql
SELECT CONCAT(
    'CREATE INDEX ', INDEX_NAME, ' ON ', TABLE_SCHEMA, '.', TABLE_NAME, ' (', GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX SEPARATOR ', '), ');'
) AS create_index_sql
FROM information_schema.STATISTICS
WHERE INDEX_NAME LIKE 'IX_LI_%'
GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME;
```

### Alternative: Generate `CREATE INDEX IF NOT EXISTS`
Since MySQL lacks `IF NOT EXISTS` for indexes, you can generate dynamic SQL in a stored procedure:

```sql
DELIMITER $$

DROP PROCEDURE IF EXISTS CreateMissingIndexes$$

CREATE PROCEDURE CreateMissingIndexes()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_sql TEXT;
    DECLARE cur CURSOR FOR
        SELECT CONCAT(
            'DO (SELECT COUNT(1) INTO @idx_exists FROM information_schema.STATISTICS ',
            'WHERE TABLE_SCHEMA = ''', TABLE_SCHEMA, ''' ',
            'AND TABLE_NAME = ''', TABLE_NAME, ''' ',
            'AND INDEX_NAME = ''', INDEX_NAME, '''); ',
            'SET @stmt = IF(@idx_exists = 0, ',
            ' ''CREATE INDEX ', INDEX_NAME, ' ON ', TABLE_SCHEMA, '.', TABLE_NAME, 
            ' (', GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX SEPARATOR ', '), ')'', NULL); ',
            'PREPARE stmt FROM @stmt; EXECUTE stmt; DEALLOCATE PREPARE stmt;'
        ) AS create_index_sql
        FROM information_schema.STATISTICS
        WHERE INDEX_NAME LIKE 'IX_LI_%'
        GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO v_sql;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SET @query = v_sql;
        PREPARE stmt FROM @query;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;

    CLOSE cur;
END$$

DELIMITER ;

CALL CreateMissingIndexes();
```

### Explanation:
1. It loops through all indexes in `information_schema.STATISTICS` that start with `'IX_LI_%'`.
2. Checks if the index exists using `SELECT COUNT(1) INTO @idx_exists`.
3. If the index does not exist, it constructs the `CREATE INDEX` statement.
4. Executes the statement dynamically.

This procedure ensures that indexes are only created if they don't already exist. You can run `CALL CreateMissingIndexes();` to execute it.

Would you like this modified to ignore certain databases or tables?