MySQL Export Indexes using a SQL query

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