List:General Discussion« Previous MessageNext Message »
From:Sergei Golubchik Date:February 27 2003 9:15am
Subject:Re: CREATE INDEX is sooo slow! any ideas?
View as plain text  
Hi!

On Feb 27, Sebastian Stan wrote:
> 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) ;

It's wrong in two ways. First, each time you add an index, MySQL has to
rebuild the index file - and all existing indexes as well!
So index ANO gets rebuilt 5 times, index BNO - 4 times, etc !
Second - index ANO is absolutely not necessary as it's the prefix of
index ANOCTYDAT. It's only wasting space and time.

To build indexes use

ALTER TABLE FACTDET20028 ADD INDEX BNO (BNO), ADD INDEX CTY (CTY),
              ADD INDEX DAT (DAT), ADD INDEX ANOCTYDAT (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.

Still that ALTER TABLE shouldn't be any better than creating indexes on
empty table before load data.

What SHOW PROCESSLIST says ? It should be repair-by-sorting.
 
Regards,
Sergei

-- 
MySQL Development Team
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik <serg@stripped>
 / /|_/ / // /\ \/ /_/ / /__  MySQL AB, http://www.mysql.com/
/_/  /_/\_, /___/\___\_\___/  Osnabrueck, Germany
       <___/
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