Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have my mysql data and index files on disk1

I need to reindex the table and do NOT have enough disk space on disk1

How do I reindex a table on disk1 and use disk2 for the reindex disk space?

For example, table foo is on disk1. I need to add an index on acctnum but do not have enough disk space on disk1. Therefore, I want to reindex on disk2 but keep the data/index files on disk1

alter table foo add index (acctnum);

edits: (by jcolebrand from an answer)

MyISAM is the engine

MySQL version is 5.0.95-log

I need the database operational during the reindex process, unless there is a quick method to load 4.5 million records.

share|improve this question

migrated from Aug 21 '12 at 11:56

This question came from our site for professional and enthusiast programmers.

Do you need to keep the Database operational during this action? Also, what is the storage engine for the table? And what version of MySQL? – Mike Brant Aug 20 '12 at 15:06
Is this table MyISAM or InnoDB ??? – RolandoMySQLDBA Aug 21 '12 at 15:37

1 Answer 1

There is the Percona Tool called pt-online-schema-change

If you can tolerate downtime, you can try the following:

Suppose you have the following

  • datadir is /var/lib/mysql
  • MyISAM table named mydb.mytable
  • You have large disk volume mounted on /backup

You could do the following

mysql -u... -p... -e"CREATE TABLE mydb.mynewtable LIKE mydb.mytable"
mysql -u... -p... -e"ALTER TABLE mydb.mynewtable ADD INDEX (acctnum)"
# Create Symlinks Pointing to Another Disk
cp /var/lib/mysql/mydb/mynewtable.MY[ID] /backup/.
rm -f /var/lib/mysql/mydb/mynewtable.MY[ID]
chown mysql:mysql /backup/mynewtable.*
ln -s /backup/mynewtable.MYD /var/lib/mysql/mydb/mynewtable.MYD
ln -s /backup/mynewtable.MYI /var/lib/mysql/mydb/mynewtable.MYI
mysql -u... -p... -e"INSERT INTO mydb.mynewtable SELECT * FROM mydb.mytable"
mysql -u... -p... -e"ALTER TABLE mydb.mytable RENAME mydb.myoldtable"
# Erase Symlinks
rm -f /var/lib/mysql/mydb/mynewtable.MY[ID]
# Move Newly Made Table Back
mv /backups/*.MY[ID] /var/lib/mysql/mydb/.
mysql -u... -p... -e"FLUSH TABLES"

Seems like a lot of work, eh ??? Try the pt-online-schema-change first.

UPDATE 2012-08-22 11:56 EDT

I am not sure what would happen, but please hear me out on this suggestion: Try executing a repair on an empty .MYI file.

How do you do that? Take my idea from my first suggestion and augment to swap the new and old .MYI.

mysql -u... -p... -e"CREATE TABLE mydb.mynewtable LIKE mydb.mytable"
mysql -u... -p... -e"ALTER TABLE mydb.mynewtable ADD INDEX (acctnum)"
cp /var/lib/mysql/mydb/mytable.MYI /backup/.
rm -f /var/lib/mysql/mydb/mytable.MYI
cp /var/lib/mysql/mydb/mynewtable.MYI /var/lib/mysql/mydb/mytable.MYI
mysql -u... -p... -e"REPAIR TABLE mydb.mytable"

This should perform an in-place linear index rebuild.

Give it a Try !!!

share|improve this answer
You are creating the index before you insert the data. This takes a lot of time. I suspect you get a better, smaller index if you create an index after you have inserted the data. is this true? – miracle173 Aug 22 '12 at 6:28
@miracle173 not really. There would still be a full copy and append to a temp MYI. I added another suggestion to attempt a linear rebuild of the index. I actually complained about this several years ago to MySQL. They implemented a workaround for index rebuilding ( See my post ) – RolandoMySQLDBA Aug 22 '12 at 16:23
How does pt-online-schema change address the issue of not having enough space in his datadir disk? – atxdba Aug 22 '12 at 16:32
@atxdba I merely suggested its use since the original poster wanted the database operational while reindexing. You can specify temp table renaming options. Thus, you can have pt-online-schema-change work its changes on another disk. It would be a little more insane to script it like I did that to have pt-online-schema-change do it. – RolandoMySQLDBA Aug 22 '12 at 16:38

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.