Re: CREATE INDEX is sooo slow! any ideas?

[Sebastian Stan]

OK..... but what about this :
(AND i don't have a fulltext field !)

Here's my table:

(lac CHAR (3),
     ano  CHAR (7),
     bno  CHAR (18),
     cty  CHAR (4),
     dat  CHAR (8),
     tim  CHAR (6),
     dur  CHAR (8),
     ccu  CHAR (8),
     loc  CHAR (20),
     ccl  CHAR (12),
     isdn CHAR (1),
     ddi  char(4));

..which have 5-6mil records.

When I do the following  it takes 5-6 hours. After the index it's done the
processes list shows mysqld-nt.exe (i use Win2k Server) with a lot of Mem.
Usage. Usually it uses 3-4,000k . When i create the index, it's goes to
11,000k and after it's done it takes about a DAY!!! to go down to 3-4,000.
You can imagine how frustrating the users are and how my phone gets on fire
:)


CREATE INDEX ANO ON FACTDET20028 (ANO) ;
CREATE INDEX BNO ON FACTDET20028 (BNO) ;
CREATE INDEX CTY ON FACTDET20028 (CTY) ;
CREATE INDEX DAT ON FACTDET20028 (DAT) ;
CREATE INDEX ANOCTYDAT ON FACTDET20028 (ANO, CTY, DAT) ;


I've tried to create the indexes two ways :
1.before loading the date  into table ("LOAD DATA local INFILE ... ")
2. after that.


Both ways it's the same thing.


----------------------------------------------
          Sebastian Stan
     s.stan@stripped