List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:February 15 2000 6:15am
Subject:Why does mysql create one index file for each table?
View as plain text  
>>>>> "Shangwu" == Shangwu Qi <sqi@stripped> writes:

Shangwu> Hi,
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.

Hi!

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..

Regards,
Monty

Thread
...A LITTLE IDEA TO OPTIMIZE SELECT STATEMENT .....Nicola Cisternino14 Feb
  • Alternative Character SetsAndrew G Milne14 Feb
    • Re: Alternative Character Setssinisa14 Feb
  • Re: ...A LITTLE IDEA TO OPTIMIZE SELECT STATEMENT .....sinisa14 Feb
  • Why does mysql create one index file for each table?Shangwu Qi14 Feb
    • Why does mysql create one index file for each table?Michael Widenius15 Feb
  • ...A LITTLE IDEA TO OPTIMIZE SELECT STATEMENT .....Michael Widenius15 Feb
  • Re: Alternative Character SetsArion16 Feb