MySQL MyISAM - How do I reindex on a separate disk

Posted on 2012-08-20
Medium Priority
Last Modified: 2012-11-14
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);
Question by:phpzilla
1 Comment
LVL 24

Accepted Solution

johanntagle earned 2000 total points
ID: 38314174
I don't think you can.  The way MySQL does this is to basically recreate the table on another file (or use additional space in the ibdata, in case you're on innodb and not using file per table).  Then once done, the newly cloned copy that has the new index is the one that is retained and the old copy is dropped.  MySQL cannot achieve this if it will allow you to use another disk for the process.  That's why I always keep space equivalent to the size of the biggest table and its indexes available on the data partition.

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month16 days, 3 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question