In the last episode (Feb 16), Michael T. Babcock said:
> > At 06:27 PM 2/16/2003 -0500, Peter Grigor wrote:
> > > Well, MySql stores all its index information in one index file,
> > > so when you add another index it has to rebuild the WHOLE file.
> > > :)
>
> Anyone on the MySQL team feel like explaining that design decision,
> besides historical reasons? I doubt its any more efficient except in
> file descriptor usage (although I've expressed the same doubts about
> InnoDB's avoidance of the filesystem too).
Which decision, putting all the indexes in one file, or rebuilding all
indexes whenever you ALTER TABLE or add an index? If the latter, I
agree with you. Modifying unrelated indexes or columns should not
force a rebuild of every index.
But on the first point, consider the fd usage in the case of, say, a
10-table database with 5 indices per table, with 20 concurrent users.
That'd be a max of 10*5*20=1000 fds open, instead of 10*2*20=400 with
Mysql's current method, and those are pretty conservative values (small
table, not many indexes, not many users).
Also there's not much benefit in separating the indexes from each
other. With different files for index and data, you can move the
indexes to another disk and get a nice speed boost (since you're not
ping-ponging the disk head between index and data). But since MySQL
can only use one index per table per query anyway, you don't gain
anything by splitting the indexes.
--
Dan Nelson
[EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
