MySQL Bugs: #46881: CREATE INDEX & specify idx directory

Bug #46881	CREATE INDEX & specify idx directory
Submitted:	24 Aug 2009 7:44	Modified:	24 Aug 2009 8:56
Reporter:	P R	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	5+	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	INDEX

Description:
feature request:
allow user to specify a directory when using the "CREATE INDEX" and/or "ALTER TABLE ADD INDEX" statements, in order to specify a directory OTHER than 
1) the default mysql db/index directory, or 
2) the directory which was (optionally) specified during table creation, using the 'INDEX DIRECTORY' option available with 'CREATE TABLE' statement...

How to repeat:
create a database table (optionally specifying the index directory):

CREATE TABLE my_table(columns) INDEX DIRECTOY '/data/idx';

next, assume the need to create a new index on existing table, with the requirement to store the new index in an alternate location, 
yet *not* in previously specified '/data/idx' directory or in the default mysql storage directory...

apparently, neither 'ALTER TABLE' nor 'CREATE INDEX' allow the user to specify the directory for the new index.

Suggested fix:
add option to 'CREATE INDEX'/'ALTER TABLE' statements to allow the user to specify a new, alternate directory for the index that's to be created.

justification for this feature is to allow user to create a new index on separate, faster storage (i.e., separate physical disk), and thus improve index creation&usage performance 'from the get-go', instead of forcing user to create all indexes within existing index directory & later manually move indexes elsewhere.

MyISAM (the only storage engine that supports INDEX DIRECTORY clause) uses single .MYI file for ALL indexes. So there is almost no point to add separate INDEX DIRECTORY clause while creating new indexes.

Please, read the manual, http://dev.mysql.com/doc/refman/5.1/en/create-table.html.

understood.my mistake, apologies...