Can I specify a different disk for a specific MySQL table?

I use my home machine as my personal database for my hobby analytics project. This machine has an SSD for the main drive where MySQL stores its data. It also has a secondary hard drive that is larger and not an SSD. I am about to create a table that I fear might be too large to fit on my SSD; is it possible for me to instead store that one table on the larger (but slower) drive? If it matters, I'm generally using MyISAM tables but could be persuaded to use InnoDB if that would help.

Migrating s single MyISAM table to another disk is only possible in Linux versions, not Windows, of MySQL with the DATA DIRECTORY and INDEX DIRECTORY clauses of ALTER TABLE on a MyISAM table.

However, in Windows, you can manually move the .MYD and .MYI files to where you want.
UPDATE 2012-01-03 22:03 EDT

Interestinging, MySQL 5.5.15 on my Windows 7 machines says symlink support exists:

mysql> show variables like 'have_sym%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_symlink  | YES   |
+---------------+-------+
1 row in set (0.09 sec)

I also discovered that symlinking is possible in Windows:

• http://en.wikipedia.org/wiki/NTFS_symbolic_link

• http://en.wikipedia.org/wiki/Symbolic_link#Windows_7_.26_Vista_symbolic_link

On my Windows 7 machines at home, the command line utility mklink exists:

C:\Windows\system32>mklink
Creates a symbolic link.

MKLINK [[/D] | [/H] | [/J]] Link Target

        /D      Creates a directory symbolic link.  Default is a file
                symbolic link.
        /H      Creates a hard link instead of a symbolic link.
        /J      Creates a Directory Junction.
        Link    specifies the new symbolic link name.
        Target  specifies the path (relative or absolute) that the new link
                refers to.

C:\Windows\system32>

I AM SHOCKED !!! That means you can experiment with CREATE TABLE ... DATA DIRECTORY='...' INDEX DIRECTORY='...' in Windows.

I just tried this:

use test
drop table if exists data_table;
drop table if exists data_table_sharded;
CREATE TABLE data_table (a int,primary key(a)) ENGINE=MyISAM;
CREATE TABLE data_table_sharded LIKE data_table;
ALTER TABLE data_table_sharded DATA DIRECTORY='C:\DAT' INDEX DIRECTORY='C:\NDX';

I just got this:

mysql> use test
Database changed
mysql> drop table if exists data_table;
Query OK, 0 rows affected (0.02 sec)

mysql> drop table if exists data_table_sharded;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE data_table (a int,primary key(a)) ENGINE=MyISAM;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE data_table_sharded LIKE data_table;
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER TABLE data_table_sharded DATA DIRECTORY='C:\DAT' INDEX DIRECTORY='C:\NDX';
Query OK, 0 rows affected, 2 warnings (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 2

mysql> show warnings;
+---------+------+----------------------------------+
| Level   | Code | Message                          |
+---------+------+----------------------------------+
| Warning | 1618 |  option ignored  |
| Warning | 1618 |  option ignored |
+---------+------+----------------------------------+
2 rows in set (0.00 sec)

mysql>

You still cannot use the DATA DIRECTORY AND INDEX DIRECTORY option in CREATE TABLE or ALTER TABLE.
UPDATE 2012-01-03 22:45 EDT

I ran these commands

use test
drop table if exists data_table;
drop table if exists data_table_sharded;
CREATE TABLE data_table (a int,primary key(a)) ENGINE=MyISAM;
INSERT INTO data_table VALUES (71),(22),(128),(97),(18),(4),(112),(277);
CREATE TABLE data_table_sharded LIKE data_table;

I made two directories

• mkdir C:\dat

• mkdir C:\ndx

I created those folders and created hard links for data_table_sharded

C:\MySQL\data\test>mklink /H data_table_sharded.MYD C:\dat\data_table_sharded.MYD
Hardlink created for data_table_sharded.MYD > C:\dat\data_table_sharded.MYD

C:\MySQL\data\test>mklink /H data_table_sharded.MYI C:\ndx\data_table_sharded.MYI
Hardlink created for data_table_sharded.MYI > C:\ndx\data_table_sharded.MYI

C:\MySQL\data\test>

I went back to MySQL and loaded the data from data_table:

mysql> flush tables;
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO data_table_sharded SELECT * FROM data_table;
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> show create table data_table_sharded\G
*************************** 1. row ***************************
       Table: data_table_sharded
Create Table: CREATE TABLE `data_table_sharded` (
  `a` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select * from data_table_sharded;
+-----+
| a   |
+-----+
|   4 |
|  18 |
|  22 |
|  71 |
|  97 |
| 112 |
| 128 |
| 277 |
+-----+
8 rows in set (0.00 sec)

mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)

mysql>

So, it can be done. Just use mklink to create hard links instead of symlinks. WOW !!!

I learned something about MySQL for Windows. I doubt if Oracle will implement the DATA DIRECTORY and INDEX DIRECTORY options since the default storage engine is now InnoDB.

Notwithstanding, you create the empty table, move the .MYD and .MYI to different folders from the OS, create hard links, run FLUSH TABLES; and INSERT your data.

Give it a Try, and let me know how it goes...

StackExchange Database Administrators Q#10108, answer score: 3