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.

Solution 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!

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

Solution 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.

Solution 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.

Solution 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.

View more solutions

Comments

[Tom]

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

[Lee Goddard]

But no FULLTEXT indexes?

[user109764]

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

[Fariman Kashani]

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