Sorry, my mistake. It's "reindexing" then. Once i should add 2 key indexes on a huge table. But instead of alter the table in 1 single query, I build each index 1 by 1.
Generally, it is faster to build all your indexes with a single ALTER TABLE statement than to build them one by one with separate ALTER TABLE statements.
And the responses of the slaves also great, too. Anyway, im using the standard my-medium.cnf setup. The huge table/db also located on another drive/partition.
"Me fail English? That's unpossible" ###___Archon___###
----- Original Message ----- From: "Paul DuBois" <[EMAIL PROTECTED]> To: "Dominicus Donny" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Wednesday, July 09, 2003 11:22 AM Subject: Re: Faster reindexing
dropAt 11:23 +0700 7/9/03, Dominicus Donny wrote: >Try analyze your table(s).
What information will this yield to make indexing faster?
> >"Me fail English? That's unpossible" >###___Archon___### > >----- Original Message ----- >From: "electroteque" <[EMAIL PROTECTED]> >To: "Paul DuBois" <[EMAIL PROTECTED]>; "Florian Weimer" <[EMAIL PROTECTED]>; ><[EMAIL PROTECTED]> >Sent: Wednesday, July 09, 2003 10:23 AM >Subject: RE: Faster reindexing > > >> when reimporting or reinserting or whatever from a huge db i usuallyapplications>> all the indexes reimport then create them again much quicker >> >> -----Original Message----- >> From: Paul DuBois [mailto:[EMAIL PROTECTED] >> Sent: Wednesday, July 09, 2003 1:09 PM >> To: Florian Weimer; [EMAIL PROTECTED] >> Subject: Re: Faster reindexing >> >> >> At 9:39 +0200 7/7/03, Florian Weimer wrote: >> >I've got a table with 100 million rows and need some indexes on it >> >(one row is 126 bytes). >> > >> >I'm currently using MyISAM and the indexing proceeds at an >> >astonishingly low rate: about 200 MB per hour. This is rate is far >> >too low; if we had to recover the database for some reason, we'd have >> >to wait for days. >> > >> >The table looks like this: >> > >> >CREATE TABLE flows ( >> > version CHAR NOT NULL, >> > router CHAR(15) NOT NULL, >> > src_ip CHAR(15) NOT NULL, >> > dst_ip CHAR(15) NOT NULL, >> > protocol TINYINT UNSIGNED NOT NULL, >> > src_port MEDIUMINT UNSIGNED NOT NULL, >> > dst_port MEDIUMINT UNSIGNED NOT NULL, >> > packets INTEGER UNSIGNED NOT NULL, >> > bytes INTEGER UNSIGNED NOT NULL, >> > src_if MEDIUMINT UNSIGNED NOT NULL, >> > dst_if MEDIUMINT UNSIGNED NOT NULL, >> > src_as MEDIUMINT UNSIGNED NOT NULL, >> > dst_as MEDIUMINT UNSIGNED NOT NULL, >> > src_net CHAR(1) NOT NULL, >> > dst_net CHAR(1) NOT NULL, >> > direction CHAR(1) NOT NULL, >> > class CHAR(1) NOT NULL, >> > start_time CHAR(24), >> > end_time CHAR(24) >> >); >> > >> >Indexes are created using this statement: >> > >> >mysql> ALTER TABLE flows >> > -> ADD INDEX dst_ip (dst_ip, src_ip), >> > -> ADD INDEX dst_port (dst_port, start_time), >> > -> ADD INDEX src_ip (src_ip, start_time), >> > -> ADD INDEX time (start_time); >> > >> >In theory, we could represent the columns router, src_ip, dst_ip, >> >start_time, end_time using integers of the appropriate size, but this >> >would make ad-hoc queries harder to type (and porting ourmight>> >would be even more difficult). >> >> Perhaps, but as a test, you might add a couple of extra columns to >> the table, then populate them like this after loading the table: >> >> UPDATE flows SET int_src_ip = INET_ATON(src_ip), int_dst_ip = >> INET_ATON(dst_ip); >> >> Then try creating the indexes using int_src_ip and int_dst_ip rather >> than src_ip and dst_ip. >> >> If it's significantly faster, you may want to reconsider whether it> Are you MySQL certified? http://www.mysql.com/certification/>> not be worth using INET_ATON(X) in your queries rather than X. >> >> > >> >Should I switch to another table type? >> >> It's easy enough to convert the table to, e.g., InnoDB and then >> create the indexes, so an empirical test should not be difficult. >> >> -- >> Paul DuBois, Senior Technical Writer >> Madison, Wisconsin, USA >> MySQL AB, www.mysql.com >> > > Are you MySQL certified? http://www.mysql.com/certification/
-- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com
-- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com
Are you MySQL certified? http://www.mysql.com/certification/
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
