Feature request: Remove indexes from table

[erkie]

Large tables with indexes take a lot longer to import than without index. The net time to import without index, then recreate after data load, is about 1/3 of the. One scenario this script could solve is to finf the indexes of a table and just remove them.

[sameerpanjwani]

I agree, this would be a really useful feature.

[erkie]

FWIW, I solved it by piping mysqldump through sed in search of a comment, to manually drop the index before the data dump actually starts. Part of my setup-replica script can be found here:

mysqldump --no-autocommit \
	--password="${EXPORTER_PASS}" \
	-h $MASTER_HOST \
	-u $EXPORTER_USER \
	--skip-lock-tables \
	--single-transaction \
	--flush-logs \
	--hex-blob \
	--master-data=2 \
	-A |
sed '/^-- Dumping data for table `the_large_table`/a \
	ALTER TABLE the_large_table DROP PRIMARY KEY, \
	DROP INDEX \`a_field_idx\`, \
	DROP INDEX \`b_field_idx\`; \
	' > "${dump_file}"

log "Extracting master positions"
the_master_log_file=`head -n80 "${dump_file}" | grep "MASTER_LOG_FILE" |  cut -d'=' -f 2 | cut -d"'" -f2`
the_master_log_pos=`head -n80 "${dump_file}" | grep "MASTER_LOG_POS" |  cut -d'=' -f 3 | cut -d';' -f 1`

log "Ensure dump successfully removes primary key"
grep -q "ALTER TABLE the_large_table DROP PRIMARY KEY" "${dump_file}"

log "Running import"
run_on_local <<-SQL
	CREATE DATABASE ${TARGET_DATABASE_NAME};
	USE ${TARGET_DATABASE_NAME};
	SOURCE ${dump_file};
SQL

log "Adding indexes"
run_on_local <<-SQL
	ALTER TABLE ${TARGET_DATABASE_NAME}.the_large_table ADD PRIMARY KEY (c_field),
	ADD INDEX a_field_idx (a_field),
	ADD INDEX b_field_idx (b_field);
SQL