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 <[EMAIL PROTECTED]>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
<___/
---------------------------------------------------------------------
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
