Thinking in programming

Things I like to talk about programming

MySQL Export Indexes using a SQL query

with 5 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
About these ads

Written by roger.padilla

December 2, 2008 at 17:54

5 Responses

Subscribe to comments with RSS.

  1. Thank you, just what I needed, the single quotes, however have been replaced to shifted ones, presumably by wordpress. One should replace them to simple ‘s.

    rav

    December 29, 2008 at 12:24

  2. jlQIpc Thanks for good post

    johnny

    December 30, 2008 at 19:44

  3. great ! worked like a charm !

    Thanks

    Radu

    February 23, 2012 at 05:14

  4. Dont work in Mysql 5.1.66

    Error
    SQL-query:

    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 ;

    reaply MySQL:

    #1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘TABLE `’,
    TABLE_NAME,
    ‘` ‘,
    ‘MODIFY COLUMN `’,
    COLUMN_NAME,
    ‘` ‘ at line 3

    chillivilli

    November 15, 2013 at 02:45


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: