Thinking in programming

Things I like to talk about programming

Archive for the ‘SQL’ Category

SQL to add just the New Indexes (filtering the existing ones)

with one comment

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`;

Written by roger.padilla

January 5, 2009 at 14:15

Posted in MySQL

Tagged with , ,

MySQL Export Indexes using a SQL query

with 6 comments

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 have the same indexes (same name, same fields and same type). So, you might be facing a problem, because of MySQL does not provide an automatic way to export the indexes from a database.

Solution

This document expose a way to extract the indexes from a database (Source Database) for use those in another database (Destination Database), using a pure SQL solution.

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 an html file in your local hard drive.
  3. Run the query 2 in the Source Database and export the resultset as an html file in your local hard drive.
  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 remove the auto_increment conditions.
  5. Run the query 4 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 remove the indexes.
  6. Open the html file exported in the step 2, copy all the statements and execute those in the Destination Database.
    This will add the auto_increment conditions from the source database.
  7. Open the html file exported in the step 3, copy all the statements and execute those in the Destination Database.
    This will add the indexes from the source database.

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’. If you don’t want to use MySQL Query Browser, you can use “INTO OUTFILE” directly in MySQL to export the resultset into a CSV file in your server and then copy it, just something like: SELECT … INTO OUTFILE ‘/result.sql’ FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘rn’ FROM …

Queries

  1. Produce the SQL to Back Up the Creation of the Auto Increment Fields.
    SELECT
    CONCAT(
    ‘ALTER TABLE `’,
    TABLE_NAME,
    ‘` ‘,
    ‘MODIFY COLUMN `’,
    COLUMN_NAME,
    ‘` ‘,
    IF(UPPER(DATA_TYPE) = ‘INT’,
    REPLACE(
    SUBSTRING_INDEX(
    UPPER(COLUMN_TYPE),
    ‘)’,
    1
    ),
    ‘INT’,
    ‘INTEGER’
    ),
    UPPER(COLUMN_TYPE)
    ),
    ‘) UNSIGNED NOT NULL AUTO_INCREMENT;’
    )
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = ‘source_database_name’ AND
    EXTRA = UPPER(‘AUTO_INCREMENT’)
    ORDER BY TABLE_NAME ASC
  2. Produce the SQL to Back Up the Creation of the Indexes.
    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
  3. Produce the SQL to Remove the AUTO_INCREMENT Condition from the Fields.
    SELECT
    CONCAT(
    ‘ALTER TABLE `’,
    TABLE_NAME,
    ‘` ‘,
    ‘MODIFY COLUMN `’,
    COLUMN_NAME,
    ‘` ‘,
    IF(UPPER(DATA_TYPE) = ‘INT’,
    REPLACE(
    SUBSTRING_INDEX(
    UPPER(COLUMN_TYPE),
    ‘)’,
    1
    ),
    ‘INT’,
    ‘INTEGER’
    ),
    UPPER(COLUMN_TYPE)
    ),
    ‘) UNSIGNED NOT NULL;’
    )
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = ‘destination_database_name’ AND
    EXTRA = UPPER(‘AUTO_INCREMENT’)
    ORDER BY TABLE_NAME ASC
  4. Produce the SQL to Delete All the Indexes.
    SELECT
    CONCAT(
    ‘ALTER TABLE `’,
    TABLE_NAME,
    ‘` ‘,
    GROUP_CONCAT(
    DISTINCT
    CONCAT(
    ‘DROP ‘,
    IF(UPPER(INDEX_NAME) = ‘PRIMARY’,
    ‘PRIMARY KEY’,
    CONCAT(‘INDEX `’, INDEX_NAME, ‘`’)
    )
    )
    SEPARATOR ‘, ‘
    ),
    ‘;’
    )
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = ‘destination_database_name’
    GROUP BY TABLE_NAME
    ORDER BY TABLE_NAME ASC

Written by roger.padilla

December 2, 2008 at 17:54