>>>>> "Shangwu" == Shangwu Qi <sqi@stripped> writes:
Shangwu> When I looked at the data directory of mysql database,
Shangwu> I found only one index file per table though the table has
Shangwu> many indexes. So adding or removing some index is very
Shangwu> slow if the table is very big volume.
Shangwu> I wonder why mysql doesn't create each file for each index.
Shangwu> Because it should be much faster for query and index maintenance.
The reason for the using just one index file is to keep down the
number of open files and also make the ISAM files easier to
administrate (copy, backup ...)
Note that manipulation one file is in many cases faster than having to
manipulate many files as your data will be closer on disk.
(This is of course not necessary true if you would use the possibility
to link different indexes to different disk)
Anyway, adding an extra index will be as fast to one file as to
many. Dropping an index is a bit slower, but not that much
slower than unlinking index from the one file.
The reason MySQL is slow with CREATE INDEX / DROP INDEX today is that
it does a full table copy + recration of all indexes when this
happens. We plan however to add optimization of this in the future..