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]
