Re: CREATE INDEX is sooo slow! any ideas?
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
Re: CREATE INDEX is sooo slow! any ideas?
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 [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
Re: CREATE INDEX is sooo slow! any ideas?
Hi! On Feb 16, Dan Nelson wrote: 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. Of course not. And it won't eventually - it's in the todo. 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
Re: CREATE INDEX is sooo slow! any ideas?
Hi! On Feb 16, david wrote: Hi I have one table with 12 million records. I'm trying to create an index on one of the fields and it's taking hours ( over 6 so far)! Am I doing something wrong? The command I issued was create index title on article ( title); The table files are -rwxrwxrwx1 mysqlmysql8646 Feb 14 17:32 article.frm -rwxrwxrwx1 mysqlmysql 1406663720 Feb 14 23:27 article.MYD -rwxrwxrwx1 mysqlmysql 69661696 Feb 14 23:27 article.MYI As you can see the main file is 1.4Gb The table definition is mysql show columns from article; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | ID| bigint(20) | YES | | NULL| | | id2 | bigint(20) | YES | | NULL| | | title | varchar(250) | YES | | NULL| | | body | text | YES | MUL | NULL| | +---+--+--+-+-+---+ 4 rows in set (0.00 sec) I'm running this on linux redhat 7.3, P500Mhz 512Mb ram -- MySQL version 3.23.53a Use MySQL-4.x In 3.23 creating FULLTEXT index can easily take months, not hours. In 4.0 this process is ~80-100 times faster. 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
Re: CREATE INDEX is sooo slow! any ideas?
Sergei Golubchik wrote: 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. Of course not. And it won't eventually - it's in the todo. Wow, I love this list (and the MySQL team). Go to bed with a question, wake up with an answer. Well, in EST5EDT at least. Thanks. -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - 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
CREATE INDEX is sooo slow! any ideas?
Hi I have one table with 12 million records. I'm trying to create an index on one of the fields and it's taking hours ( over 6 so far)! Am I doing something wrong? The command I issued was create index title on article ( title); The table files are -rwxrwxrwx1 mysqlmysql8646 Feb 14 17:32 article.frm -rwxrwxrwx1 mysqlmysql 1406663720 Feb 14 23:27 article.MYD -rwxrwxrwx1 mysqlmysql 69661696 Feb 14 23:27 article.MYI As you can see the main file is 1.4Gb The table definition is mysql show columns from article; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | ID| bigint(20) | YES | | NULL| | | id2 | bigint(20) | YES | | NULL| | | title | varchar(250) | YES | | NULL| | | body | text | YES | MUL | NULL| | +---+--+--+-+-+---+ 4 rows in set (0.00 sec) I'm running this on linux redhat 7.3, P500Mhz 512Mb ram -- MySQL version 3.23.53a Thanks David - 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
Re: CREATE INDEX is sooo slow! any ideas?
That body column wouldn't happen to be a fulltext index, would it? :) If so, 'ave another coffee. Peter ^_^ - Peter Grigor Hoobly Free Classifieds http://www.hoobly.com - Original Message - From: david [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, February 16, 2003 5:47 PM Subject: CREATE INDEX is sooo slow! any ideas? Hi I have one table with 12 million records. I'm trying to create an index on one of the fields and it's taking hours ( over 6 so far)! Am I doing something wrong? The command I issued was create index title on article ( title); The table files are -rwxrwxrwx1 mysqlmysql8646 Feb 14 17:32 article.frm -rwxrwxrwx1 mysqlmysql 1406663720 Feb 14 23:27 article.MYD -rwxrwxrwx1 mysqlmysql 69661696 Feb 14 23:27 article.MYI As you can see the main file is 1.4Gb The table definition is mysql show columns from article; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | ID| bigint(20) | YES | | NULL| | | id2 | bigint(20) | YES | | NULL| | | title | varchar(250) | YES | | NULL| | | body | text | YES | MUL | NULL| | +---+--+--+-+-+---+ 4 rows in set (0.00 sec) I'm running this on linux redhat 7.3, P500Mhz 512Mb ram -- MySQL version 3.23.53a Thanks David - 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 - 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
Re: CREATE INDEX is sooo slow! any ideas?
Another coffee ? I'd buy Starbucks . Jerry - Original Message - From: Peter Grigor [EMAIL PROTECTED] To: [EMAIL PROTECTED]; david [EMAIL PROTECTED] Sent: Sunday, February 16, 2003 10:58 PM Subject: Re: CREATE INDEX is sooo slow! any ideas? That body column wouldn't happen to be a fulltext index, would it? :) If so, 'ave another coffee. Peter ^_^ - Peter Grigor Hoobly Free Classifieds http://www.hoobly.com - Original Message - From: david [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, February 16, 2003 5:47 PM Subject: CREATE INDEX is sooo slow! any ideas? Hi I have one table with 12 million records. I'm trying to create an index on one of the fields and it's taking hours ( over 6 so far)! Am I doing something wrong? The command I issued was create index title on article ( title); The table files are -rwxrwxrwx1 mysqlmysql8646 Feb 14 17:32 article.frm -rwxrwxrwx1 mysqlmysql 1406663720 Feb 14 23:27 article.MYD -rwxrwxrwx1 mysqlmysql 69661696 Feb 14 23:27 article.MYI As you can see the main file is 1.4Gb The table definition is mysql show columns from article; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | ID| bigint(20) | YES | | NULL| | | id2 | bigint(20) | YES | | NULL| | | title | varchar(250) | YES | | NULL| | | body | text | YES | MUL | NULL| | +---+--+--+-+-+---+ 4 rows in set (0.00 sec) I'm running this on linux redhat 7.3, P500Mhz 512Mb ram -- MySQL version 3.23.53a Thanks David - 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 - 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 - 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
Re: CREATE INDEX is sooo slow! any ideas?
Yes, and better go to the one across town to get it :) Peter ^_^ - Peter Grigor Hoobly Free Classifieds http://www.hoobly.com - Original Message - From: Jerry [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, February 16, 2003 6:00 PM Subject: Re: CREATE INDEX is sooo slow! any ideas? Another coffee ? I'd buy Starbucks . Jerry - Original Message - From: Peter Grigor [EMAIL PROTECTED] To: [EMAIL PROTECTED]; david [EMAIL PROTECTED] Sent: Sunday, February 16, 2003 10:58 PM Subject: Re: CREATE INDEX is sooo slow! any ideas? That body column wouldn't happen to be a fulltext index, would it? :) If so, 'ave another coffee. Peter ^_^ - Peter Grigor Hoobly Free Classifieds http://www.hoobly.com - Original Message - From: david [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, February 16, 2003 5:47 PM Subject: CREATE INDEX is sooo slow! any ideas? Hi I have one table with 12 million records. I'm trying to create an index on one of the fields and it's taking hours ( over 6 so far)! Am I doing something wrong? The command I issued was create index title on article ( title); The table files are -rwxrwxrwx1 mysqlmysql8646 Feb 14 17:32 article.frm -rwxrwxrwx1 mysqlmysql 1406663720 Feb 14 23:27 article.MYD -rwxrwxrwx1 mysqlmysql 69661696 Feb 14 23:27 article.MYI As you can see the main file is 1.4Gb The table definition is mysql show columns from article; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | ID| bigint(20) | YES | | NULL| | | id2 | bigint(20) | YES | | NULL| | | title | varchar(250) | YES | | NULL| | | body | text | YES | MUL | NULL| | +---+--+--+-+-+---+ 4 rows in set (0.00 sec) I'm running this on linux redhat 7.3, P500Mhz 512Mb ram -- MySQL version 3.23.53a Thanks David - 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 - 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 - 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 - 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
Re: CREATE INDEX is sooo slow! any ideas?
I meant buy the whole chain, its going to take a few cups Jerry - Original Message - From: Peter Grigor [EMAIL PROTECTED] To: Jerry [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Sunday, February 16, 2003 11:11 PM Subject: Re: CREATE INDEX is sooo slow! any ideas? Yes, and better go to the one across town to get it :) Peter ^_^ - Peter Grigor Hoobly Free Classifieds http://www.hoobly.com - Original Message - From: Jerry [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, February 16, 2003 6:00 PM Subject: Re: CREATE INDEX is sooo slow! any ideas? Another coffee ? I'd buy Starbucks . Jerry - Original Message - From: Peter Grigor [EMAIL PROTECTED] To: [EMAIL PROTECTED]; david [EMAIL PROTECTED] Sent: Sunday, February 16, 2003 10:58 PM Subject: Re: CREATE INDEX is sooo slow! any ideas? That body column wouldn't happen to be a fulltext index, would it? :) If so, 'ave another coffee. Peter ^_^ - Peter Grigor Hoobly Free Classifieds http://www.hoobly.com - Original Message - From: david [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, February 16, 2003 5:47 PM Subject: CREATE INDEX is sooo slow! any ideas? Hi I have one table with 12 million records. I'm trying to create an index on one of the fields and it's taking hours ( over 6 so far)! Am I doing something wrong? The command I issued was create index title on article ( title); The table files are -rwxrwxrwx1 mysqlmysql8646 Feb 14 17:32 article.frm -rwxrwxrwx1 mysqlmysql 1406663720 Feb 14 23:27 article.MYD -rwxrwxrwx1 mysqlmysql 69661696 Feb 14 23:27 article.MYI As you can see the main file is 1.4Gb The table definition is mysql show columns from article; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | ID| bigint(20) | YES | | NULL| | | id2 | bigint(20) | YES | | NULL| | | title | varchar(250) | YES | | NULL| | | body | text | YES | MUL | NULL| | +---+--+--+-+-+---+ 4 rows in set (0.00 sec) I'm running this on linux redhat 7.3, P500Mhz 512Mb ram -- MySQL version 3.23.53a Thanks David - 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 - 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 - 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 - 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
Re: CREATE INDEX is sooo slow! any ideas?
Yes it is but why would that slow down index creation on the other field. The full text index is just for the body field. Thanks At 05:58 PM 2/16/2003 -0500, Peter Grigor wrote: That body column wouldn't happen to be a fulltext index, would it? :) If so, 'ave another coffee. Peter ^_^ - Peter Grigor Hoobly Free Classifieds http://www.hoobly.com - Original Message - From: david [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, February 16, 2003 5:47 PM Subject: CREATE INDEX is sooo slow! any ideas? Hi I have one table with 12 million records. I'm trying to create an index on one of the fields and it's taking hours ( over 6 so far)! Am I doing something wrong? The command I issued was create index title on article ( title); The table files are -rwxrwxrwx1 mysqlmysql8646 Feb 14 17:32 article.frm -rwxrwxrwx1 mysqlmysql 1406663720 Feb 14 23:27 article.MYD -rwxrwxrwx1 mysqlmysql 69661696 Feb 14 23:27 article.MYI As you can see the main file is 1.4Gb The table definition is mysql show columns from article; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | ID| bigint(20) | YES | | NULL| | | id2 | bigint(20) | YES | | NULL| | | title | varchar(250) | YES | | NULL| | | body | text | YES | MUL | NULL| | +---+--+--+-+-+---+ 4 rows in set (0.00 sec) I'm running this on linux redhat 7.3, P500Mhz 512Mb ram -- MySQL version 3.23.53a Thanks David - 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 - 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
Re: CREATE INDEX is sooo slow! any ideas?
Well, MySql stores all its index information in one index file, so when you add another index it has to rebuild the WHOLE file. :) Peter ^_^ - Peter Grigor Hoobly Free Classifieds http://www.hoobly.com - Original Message - From: david [EMAIL PROTECTED] To: Peter Grigor [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Sunday, February 16, 2003 6:13 PM Subject: Re: CREATE INDEX is sooo slow! any ideas? Yes it is but why would that slow down index creation on the other field. The full text index is just for the body field. Thanks At 05:58 PM 2/16/2003 -0500, Peter Grigor wrote: That body column wouldn't happen to be a fulltext index, would it? :) If so, 'ave another coffee. Peter ^_^ - Peter Grigor Hoobly Free Classifieds http://www.hoobly.com - Original Message - From: david [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, February 16, 2003 5:47 PM Subject: CREATE INDEX is sooo slow! any ideas? Hi I have one table with 12 million records. I'm trying to create an index on one of the fields and it's taking hours ( over 6 so far)! Am I doing something wrong? The command I issued was create index title on article ( title); The table files are -rwxrwxrwx1 mysqlmysql8646 Feb 14 17:32 article.frm -rwxrwxrwx1 mysqlmysql 1406663720 Feb 14 23:27 article.MYD -rwxrwxrwx1 mysqlmysql 69661696 Feb 14 23:27 article.MYI As you can see the main file is 1.4Gb The table definition is mysql show columns from article; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | ID| bigint(20) | YES | | NULL| | | id2 | bigint(20) | YES | | NULL| | | title | varchar(250) | YES | | NULL| | | body | text | YES | MUL | NULL| | +---+--+--+-+-+---+ 4 rows in set (0.00 sec) I'm running this on linux redhat 7.3, P500Mhz 512Mb ram -- MySQL version 3.23.53a Thanks David - 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 - 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 - 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
Re: CREATE INDEX is sooo slow! any ideas?
Thanks. So, after I've travelled to Seattle to get my coffee, do you think that creating this index will speed up the select queries? Would it be faster if I had created separate tables and indexed them individually? What is the thoughts about searching text fields for text. Is it best to store the text in a text field or is it best to tokenize out the terms and create a word table with index? Thanks David 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. :) Peter ^_^ - Peter Grigor Hoobly Free Classifieds http://www.hoobly.com - Original Message - From: david [EMAIL PROTECTED] To: Peter Grigor [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Sunday, February 16, 2003 6:13 PM Subject: Re: CREATE INDEX is sooo slow! any ideas? Yes it is but why would that slow down index creation on the other field. The full text index is just for the body field. Thanks At 05:58 PM 2/16/2003 -0500, Peter Grigor wrote: That body column wouldn't happen to be a fulltext index, would it? :) If so, 'ave another coffee. Peter ^_^ - Peter Grigor Hoobly Free Classifieds http://www.hoobly.com - Original Message - From: david [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, February 16, 2003 5:47 PM Subject: CREATE INDEX is sooo slow! any ideas? Hi I have one table with 12 million records. I'm trying to create an index on one of the fields and it's taking hours ( over 6 so far)! Am I doing something wrong? The command I issued was create index title on article ( title); The table files are -rwxrwxrwx1 mysqlmysql8646 Feb 14 17:32 article.frm -rwxrwxrwx1 mysqlmysql 1406663720 Feb 14 23:27 article.MYD -rwxrwxrwx1 mysqlmysql 69661696 Feb 14 23:27 article.MYI As you can see the main file is 1.4Gb The table definition is mysql show columns from article; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | ID| bigint(20) | YES | | NULL| | | id2 | bigint(20) | YES | | NULL| | | title | varchar(250) | YES | | NULL| | | body | text | YES | MUL | NULL| | +---+--+--+-+-+---+ 4 rows in set (0.00 sec) I'm running this on linux redhat 7.3, P500Mhz 512Mb ram -- MySQL version 3.23.53a Thanks David - 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 - 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 - 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 - 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
Re: CREATE INDEX is sooo slow! any ideas?
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). SQL and all that ... -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - 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
Re: CREATE INDEX is sooo slow! any ideas?
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
