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

Question

I am working on my development database and have improved its performance.

However, to my surprise, I cannot find a way to export indexes into my production database.

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

The main problem is that I do not see sorting in indexes, so it will be difficult even to do it manually.

Answer 1

Perhaps you mean "How do I recreate the development indices of my (existing) database"?

If so, I think the SQL commands you are 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 lines "KEY" and "CONSTRAINT" from the output of "SHOW CREATE TABLE" and return it back to "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!

Answer 2

First read the tutorial here on how to export MySQL indexes using an SQL query . Further:

• If you fill in the DUMP of your database and import it into another (using PHPMyAdmin, etc.), the indexes will be restored.

• If possible, you can copy the contents of the entire MySQL database folder to the production database. It will help too, quickly. Read more in the MySQL docs here .

Answer 3

you can use the following command to dump

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

and indexes will be copied automatically.

Answer 4

I believe that you are trying to export the indices themselves, and not just the code for their regeneration in the production process, right? (I guess this is because loading generating these indexes is not favorable in most production environments.)

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

It looks like in your situation, you can first copy your good data to the testing environment. My development cycle usually follows this approach: DDL flows from testing to production through programming, and DML moves from production to testing through actual use of the system.

Answer 5

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

I changed the indexes to both of them, so I wanted to combine everything together. So I needed to compare the data with Notepad +.

Here's 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 title bar → delete all columns, but leave "database_name_table_name_table_value of the index column"

Then filter by database.

Copy everything from database1 to notepad + screen 1, excel database2 filter, copy everything to notepad + screen 2 → COMPARE!

Answer 6

@Origo answer extension. There is a case where I needed to extract 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;