when reimporting or reinserting or whatever from a huge db i usually drop 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 our applications >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 might 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/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
