Quick background

You have several instances of a database, and you adds indexes to improve the performance. Sometimes you does not adds these indexes in all the instances, or for some other reason you need to be sure that all the databases are up to date with the same indexes.

Solution

This document expose a way to extract the indexes from an up to date database (Source Database) and to add just the new Indexes into another database (filtering the already existing ones) by comparing the indexes in each database.

The workflow should be as follows:

  1. In the below SQL scripts, replace ’source_database_name’ and ‘destination_database_name’ with the name of the database in the source and destination database respectively.

  2. Run the query 1 in the Source Database and export the resultset as a CSV file in your local hard drive. By default the 2nd script in the query 2 look for the file ‘C:/Indexes_In_Src_DB.csv’.
  3. Run the query 2 in the Destination Database. This will create some auxiliar databases, which purpose is to store information util to find the not existing indexes.
    Be sure the you’ve named the CSV file as ‘C:/Indexes_In_Src_DB.csv’ or just change that string in the 2nd script in the query 2 by the name you’ve used.
  4. Run the query 3 in the Destination Database, export the resultset as a html file in your local hard drive. Open that html file, copy all the statements and execute those in that same database (Destination Database). This will add the new indexes.
  5. Run the query 4 in the Destination Database. This will delete the auxiliar databases.

Note: To export a resultset in MySQL Query Browser, execute a query, then do right click in the table -> ‘Export Resultset’ -> ‘Export as HTML File’.

Queries

  1. Produce the SQL to Create the Indexes that are in the Source Database

    SELECT
        CONCAT(
            ‘ALTER TABLE `’,
            TABLE_NAME,
            ‘` ‘,
            ‘ADD ‘,
            IF(NON_UNIQUE = 1,
                CASE UPPER(INDEX_TYPE)
                    WHEN ‘FULLTEXT’ THEN ‘FULLTEXT INDEX’
                    WHEN ‘SPATIAL’ THEN ‘SPATIAL INDEX’
                    ELSE CONCAT(‘INDEX `’, INDEX_NAME, ‘` USING ‘, INDEX_TYPE)
                END,
                IF(UPPER(INDEX_NAME) = ‘PRIMARY’,
                    CONCAT(‘PRIMARY KEY USING ‘, INDEX_TYPE),
                    CONCAT(‘UNIQUE INDEX `’, INDEX_NAME, ‘` USING ‘, INDEX_TYPE)
                )
            ),
            ‘(‘,
            GROUP_CONCAT(
                DISTINCT
                    CONCAT(‘`’, COLUMN_NAME, ‘`’)
                ORDER BY SEQ_IN_INDEX ASC
                SEPARATOR ‘, ‘
            ),
            ‘);’
         ) AS ‘Show_Add_Indexes’
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = ’source_database_name’
    GROUP BY TABLE_NAME, INDEX_NAME
    ORDER BY TABLE_NAME ASC, INDEX_NAME ASC

  2. Create Auxiliar Tables

    /* Temporary Table to store all the Indexes presents in the Source Database */

    DROP TABLE IF EXISTS `indexes_in_src_db`;

    CREATE TABLE `indexes_in_src_db` (
        `TABLE_NAME` VARCHAR(100) NOT NULL,
        `INDEX_NAME` VARCHAR(100) NOT NULL,
        `NON_UNIQUE` TINYINT(1) NOT NULL,
        `INDEX_TYPE` VARCHAR(100) NOT NULL,
        `COLUMNS_NAMES` VARCHAR(230) NOT NULL,
        UNIQUE KEY `table_name_index_name` USING BTREE(`TABLE_NAME`,`INDEX_NAME`),
        UNIQUE KEY `table_name_columns_names` USING BTREE(`TABLE_NAME`,`COLUMNS_NAMES`)
    ) ENGINE=MYISAM DEFAULT CHARSET=utf8;

    /* END */

    /* Load the indexes from the CSV file in the ‘indexes_in_src_db’ table */

    LOAD DATA INFILE ‘C:/Indexes_In_Src_DB.csv’
        INTO TABLE `indexes_in_src_db`
            FIELDS TERMINATED BY ‘,’
            OPTIONALLY ENCLOSED BY ‘"’
            LINES TERMINATED BY ‘rn‘
            IGNORE 1 LINES;

    /* END */

    /* Temporary Table to store the Indexes presents in the Destination Database */

    DROP TABLE IF EXISTS `indexes_in_dest_db`;

    CREATE TABLE `indexes_in_dest_db` (
        `TABLE_NAME` VARCHAR(100) NOT NULL,
        `INDEX_NAME` VARCHAR(100) NOT NULL,
        `NON_UNIQUE` TINYINT(1) NOT NULL,
        `INDEX_TYPE` VARCHAR(100) NOT NULL,
        `COLUMNS_NAMES` VARCHAR(230) NOT NULL,
        UNIQUE KEY `table_name_index_name` USING BTREE(`TABLE_NAME`,`INDEX_NAME`),
        UNIQUE KEY `table_name_columns_names` USING BTREE(`TABLE_NAME`,`COLUMNS_NAMES`)
    ) ENGINE=MYISAM DEFAULT CHARSET=utf8;

    /* END */

    /* Fill the ‘indexes_in_dest_db’ table with All the Indexes presents in the Destination DB */

    INSERT INTO `indexes_in_dest_db`
    SELECT
       
            TABLE_NAME,
            INDEX_NAME,
            NON_UNIQUE,
            INDEX_TYPE,
            GROUP_CONCAT(
                DISTINCT
                    CONCAT(‘`’, COLUMN_NAME, ‘`’)
                ORDER BY SEQ_IN_INDEX ASC
                SEPARATOR ‘,’
            ) AS COLUMNS_NAMES

    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = ‘destination_database_name’
    GROUP BY TABLE_NAME, INDEX_NAME;

    /* END */

    /* Temporary Table to store the Indexes presents in Src Database that are not presents in the Destination Database */

    DROP TABLE IF EXISTS `indexes_in_src_db_not_in_dest_db`;

    CREATE TABLE `indexes_in_src_db_not_in_dest_db` (
        `TABLE_NAME` VARCHAR(100) NOT NULL,
        `INDEX_NAME` VARCHAR(100) NOT NULL,
        `NON_UNIQUE` TINYINT(1) NOT NULL,
        `INDEX_TYPE` VARCHAR(100) NOT NULL,
        `COLUMNS_NAMES` VARCHAR(230) NOT NULL,
        UNIQUE KEY `table_name_index_name` USING BTREE(`TABLE_NAME`,`INDEX_NAME`),
        UNIQUE KEY `table_name_columns_names` USING BTREE(`TABLE_NAME`,`COLUMNS_NAMES`)
    ) ENGINE=MYISAM DEFAULT CHARSET=utf8;

    /* Fill the ‘indexes_in_src_db_not_in_dest_db’ table with All the Indexes presents in the Src DB that are not presents in the Dest DB */

    INSERT INTO `indexes_in_src_db_not_in_dest_db`
    SELECT
            SDB.TABLE_NAME,
            SDB.INDEX_NAME,
            SDB.NON_UNIQUE,
            SDB.INDEX_TYPE,
            SDB.COLUMNS_NAMES
    FROM indexes_in_src_db SDB
    WHERE ROW(SDB.TABLE_NAME,SDB.COLUMNS_NAMES) NOT IN(SELECT DDB.TABLE_NAME,DDB.COLUMNS_NAMES FROM indexes_in_dest_db AS DDB)
    GROUP BY SDB.TABLE_NAME, SDB.INDEX_NAME
    ORDER BY SDB.TABLE_NAME ASC, SDB.INDEX_NAME ASC;

    /* END */

  3. Produce the SQL to Add the New Indexes into the Destination Database

    SELECT
        CONCAT(
            ‘ALTER TABLE `’,
            TABLE_NAME,
            ‘` ‘,
            ‘ADD ‘,
            IF(NON_UNIQUE = 1,
                CASE UPPER(INDEX_TYPE)
                    WHEN ‘FULLTEXT’ THEN ‘FULLTEXT INDEX’
                    WHEN ‘SPATIAL’ THEN ‘SPATIAL INDEX’
                    ELSE CONCAT(‘INDEX `’, INDEX_NAME, ‘` USING ‘,INDEX_TYPE)
                END,
                IF(UPPER(INDEX_NAME) = ‘PRIMARY’,
                    CONCAT(‘PRIMARY KEY USING ‘, INDEX_TYPE),
                    CONCAT(‘UNIQUE INDEX `’, INDEX_NAME, ‘` USING ‘,INDEX_TYPE)
                )
            ),
            ‘(‘,
            COLUMNS_NAMES,
            ‘);’
         ) AS ‘ADD_Not_Existing_Indexes’
    FROM `indexes_in_src_db_not_in_dest_db`
    GROUP BY TABLE_NAME, INDEX_NAME
    ORDER BY TABLE_NAME ASC, INDEX_NAME ASC;

  4. Drop the auxiliar tables

    DROP TABLE IF EXISTS `indexes_in_src_db`;
    DROP TABLE IF EXISTS `indexes_in_dest_db`;
    DROP TABLE IF EXISTS `indexes_in_src_db_not_in_dest_db`;