Check out the EXPLAIN command "EXPLAIN tbl_name is a synonym for DESCRIBE tbl_name or SHOW COLUMNS FROM tbl_name.
When you precede a SELECT statement with the keyword EXPLAIN, MySQL explains how it would process the SELECT, providing information about how tables are joined and in which order. With the help of EXPLAIN, you can see when you must add indexes to tables to get a faster SELECT that uses indexes to find the records." http://www.mysql.com/doc/en/EXPLAIN.html Terry Spencer Haigh Consultancy Services +44 (0)2073007329 www.haigh-cs.co.uk -----Original Message----- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 5:23 AM To: Dominicus Donny; [EMAIL PROTECTED] Subject: Re: Faster reindexing At 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 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/ -- 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]
