Chris Stoughton wrote: > I have a table with a few million rows, wth 633 columns. I want to > create 10 inidices on this table, six single-column and four two-column > inidices. The database will be loaded once and remain static, and > queried many times. Please note that this is a "small" prototype for > the actual database, which will have 40 times more rows. > > I used two strategies to create these tables: > > 1. Create the indices in the "create table" statement > 2. Load the table, and then use the "create index" statement for each > index. > > With Strategy 1, the loading started nicely using the "load data > infile" command to load approx. 2000 records at a time. It took 3 > seconds for each "load data infile" command. After a few hundred "load > data infile" commands, however, the time increased to two minutes per > command. > > With Strategy 2, the loading went from start to finish at 3 seconds for > each "load data infile" command. But now, creating each index is taking > longer. Here is a log: > > Fri Sep 20 15:17:43 CDT 2002 create index objId on bestTsObj (objId) > Fri Sep 20 15:35:51 CDT 2002 create index fieldId on bestTsObj (fieldId) > Fri Sep 20 15:56:02 CDT 2002 create index targetObjId on bestTsObj > (targetObjId) > Fri Sep 20 16:20:02 CDT 2002 create index ra on bestTsObj (ra) > Fri Sep 20 16:49:16 CDT 2002 create index decl on bestTsObj (decl) > Fri Sep 20 17:53:38 CDT 2002 create index u on bestTsObj (u) > Fri Sep 20 18:42:52 CDT 2002 create index g on bestTsObj (g) > Fri Sep 20 22:04:25 CDT 2002 create index r on bestTsObj (r) > Sat Sep 21 10:06:44 CDT 2002 create index i on bestTsObj (i) > Sat Sep 21 19:31:52 CDT 2002 create index z on bestTsObj (z) > Sun Sep 22 15:45:06 CDT 2002 create index ug on bestTsObj (u,g) > > > The first index was created in a reasonable amount of time (18 minutes), > but it is taking longer to create more indices. > > > With both strategies, no process is swapping heavily, and mysqld is not > consuming available CPU cycles efficiently. > > Can you suggest how I can build multiple indices on large tables > efficiently? Will it help to create a "narrow" table with only the > quantities I want to use in indices? > > We have installed mysql via rpm: > bash-2.04$ mysqladmin > mysqladmin Ver 8.18 Distrib 3.23.36, for redhat-linux-gnu on i386 > > bash-2.04$ uname -r > 2.4.18-xfs-1.1 > bash-2.04$ > What diagnostics do you suggest I run? > > Thanks > Hi,
You should use strategy #1 since each time you create a new index, all index are rebuilt (this is why each new index is taking longer in strategy#2). Since mysqld is not consuming CPU nor swapping, it is probably doing a lot of disc I/Os. You can verify that with vmstat utility. You didn't specify how much RAM you have and what mysql configuration you are using (check /etc/my.cnf). You should pay special attention to 'key_buffer_size' parameter value since it has a major impact on index performance. Regards, -- Joseph Bueno --------------------------------------------------------------------- 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
