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).
Should I switch to another table type?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
