List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:February 17 2003 4:47am
Subject:Re: CREATE INDEX is sooo slow! any ideas?
View as plain text  
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
	dnelson@stripped
Thread
CREATE INDEX is sooo slow! any ideas?david16 Feb
  • Re: CREATE INDEX is sooo slow! any ideas?Peter Grigor16 Feb
    • Re: CREATE INDEX is sooo slow! any ideas?david17 Feb
  • Re: CREATE INDEX is sooo slow! any ideas?Jerry17 Feb
  • Re: CREATE INDEX is sooo slow! any ideas?Peter Grigor17 Feb
  • Re: CREATE INDEX is sooo slow! any ideas?Jerry17 Feb
  • Re: CREATE INDEX is sooo slow! any ideas?Peter Grigor17 Feb
    • Re: CREATE INDEX is sooo slow! any ideas?david17 Feb
      • Re: CREATE INDEX is sooo slow! any ideas?Michael T. Babcock17 Feb
        • Re: CREATE INDEX is sooo slow! any ideas?Dan Nelson17 Feb
          • Re: CREATE INDEX is sooo slow! any ideas?Sergei Golubchik17 Feb
            • Re: CREATE INDEX is sooo slow! any ideas?Michael T. Babcock17 Feb
  • Re: CREATE INDEX is sooo slow! any ideas?Sergei Golubchik17 Feb
Re: CREATE INDEX is sooo slow! any ideas?Sebastian Stan27 Feb
  • Re: CREATE INDEX is sooo slow! any ideas?Sergei Golubchik27 Feb