MySql, how can I export indexes from my development database to my production database?

Question

I've been working on my development database and have tweaked its performance.

However, to my surprise I can't find a way to export the indexes to my production database.

I thought there would be an easy way to do this. I don't want to replace the data in my production database.

The main problem is that I can't see sorting in the indexes so its going to be difficult to even do it manually.

Jacinto

Answer 1

Perhaps you mean "How do I re-create my development indexes on my (existing) live database"?

If so, I think the SQL commands you're looking for are;

SHOW CREATE TABLE {tablename};

ALTER TABLE ADD INDEX {index_name} (col1, col2)

ALTER TABLE DROP INDEX {index_name}

You can copy the "KEY" and "CONSTRAINT" rows from "SHOW CREATE TABLE" output and put it back in the "ALTER TABLE ADD INDEX".

dev mysql> SHOW CREATE TABLE city;
CREATE TABLE `city` (
  `id` smallint(4) unsigned NOT NULL auto_increment,
  `city` varchar(50) character set utf8 collate utf8_bin NOT NULL default '',
  `region_id` smallint(4) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `region_idx` (region_id),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`region_id`) REFERENCES `region` (`id`) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB;

live mysql> SHOW CREATE TABLE city;
CREATE TABLE `city` (
  `id` smallint(4) unsigned NOT NULL auto_increment,
  `city` varchar(50) character set utf8 collate utf8_bin NOT NULL default '',
  `region_id` smallint(4) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB;

live mysql> ALTER TABLE `city` ADD KEY `region_idx` (region_id);
live mysql> ALTER TABLE `city` ADD CONSTRAINT `city_ibfk_1` FOREIGN KEY (`region_id`) REFERENCES `region` (`id`) ON UPDATE CASCADE ON DELETE RESTRICT;

Hope this helps!

Uralaltaic

Answer 2

Extending on @origo's answer. There is a case where i needed to extract the DDL for a bunch of indexes. This script does the job.

source : https://rogerpadilla.wordpress.com/2008/12/02/mysql-export-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 = 'PLEASE CHANGE HERE'
GROUP BY TABLE_NAME, INDEX_NAME
ORDER BY TABLE_NAME ASC, INDEX_NAME ASC;

Carrington

You may want to replace INDEX_NAME, to '',INDEX_NAME,'' if your index names contain spaces!! – Stultz

@Carrington I get this is incompatible with sql_mode=only_full_group_by error. I fixed that by running SET sql_mode = '' – Wacke

Answer 3

First, read the tutorial here about how-to Export MySQL Indexes using a SQL query. Further:

1. If you do complete DUMP of your database and IMPORT it to another (using PHPMyAdmin, etc), the indexes will get regenerated.

2. If possible, you can copy contents of your entire MySQL database folder to the production database. This will do the trick too, quickly. Read more here at MySQL docs.

Saire

Answer 4

you can use the following command to take a dump

mysqldump -u [USERNAME] -p [DBNAME] | gzip > [/path_to_file/DBNAME].sql.gz

and indexes will be copied automatically.

Ladylove

But no FULLTEXT indexes? – Am

Answer 5

I believe you're trying to export the indexes themselves and not just the code to regenerate them in production, right? (I'm assuming this because the load of generating these indexes is not favorable in most production environments.)

The mysqldump utility is useful if performance isn't your main concern, and I use it all the time. If you're looking for a very fast method, though, I would suggest copying the actual InnoDB files from one cold database to the other (assuming they're exactly the same MySQL version with the exactly the same configuration and the exactly the same expected behavior, etc). This method is dangerous if there any differences between the systems.

It sounds like, in your situation, you might want to copy your good data to your testing environment first. My development cycle typically follows this approach: DDL flows from testing to production via programming, and DML flows from production to testing via actual use of the system.

Mailand

please provide answers instead of assumptions, This belongs to comments. – Avid

Answer 6

I also have development and production servers with the same database structure.

I modified indexes on both of them so I wanted to merge all together. So I needed to compare data with Notepad+.

Here is how you export indexes for all tables, all databases and how to filter and compare them:

--- Single table:
    SHOW INDEX FROM mydb.mytable;
    SHOW INDEX FROM mytable;

--- Multi tables, databases:
    USE information_schema;
    SELECT * FROM `statistics` ORDER BY TABLE_SCHEMA ASC, TABLE_NAME ASC, INDEX_NAME ASC;

Now, phpMyAdmin -> Export to CSV-Excel:

add a header row -> delete all columns but leave "database_name table_name index column value"

Then, filter by database.

Copy all from database1 to a notepad+ screen 1, filter excel database2, copy all to notepad+ screen 2 -> COMPARE!

Ibnsina

Answer 7

I prefer one query per table, for multiples indexes. But not work for composed indexes with more than one column. Anyway, give it a shot.

SELECT 
CONCAT
(concat("ALTER TABLE ",trim(TABLE_NAME)),
group_concat(
IF(INDEX_NAME="PRIMARY", 
concat(" ADD PRIMARY KEY (`", column_name,"`)"),
concat(" ADD INDEX `",INDEX_NAME,"` (`",column_name,"` ASC) ")
)),";") 
AS CMD
FROM information_schema.STATISTICS 
WHERE TABLE_SCHEMA='your_schema'  
GROUP BY TABLE_NAME;

Loy