Hi, thanks for the response. Here is everything you asked for. I am running 5.0.70 built from Gentoo Portage.
You mentioned something about RAM. This is a one off thing I am doing to gather data, so I am actually doing it on my laptop which is a Dell E6400. It has a Core 2 Duo T9600 2.8Ghz processor with 4G of RAM so it's not exactly a slouch, but I also havn't tweaked the my.cnf file either. Were you thinking I don't have enough RAM set aside for all the keys to live in memory?
SHOW CREATE TABLE flows;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| flows | CREATE TABLE `flows` (
`s_time` datetime default NULL,
`e_time` datetime default NULL,
`duration` float(8,3) default NULL,
`proto` varchar(4) default NULL,
`src_ip` varchar(15) default NULL,
`src_port` varchar(5) default NULL,
`dst_ip` varchar(15) default NULL,
`dst_port` varchar(5) default NULL,
`bytes` int(10) default NULL,
`idx` int(8) NOT NULL auto_increment,
PRIMARY KEY (`idx`),
KEY `stime` (`s_time`),
KEY `etime` (`e_time`)
) ENGINE=MyISAM AUTO_INCREMENT=28601713 DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
SHOW TABLE STATUS LIKE 'flows';
+-------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+-------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
| flows | MyISAM | 10 | Dynamic | 28601712 | 77 | 2203861036 | 281474976710655 | 1173725184 | 0 | 28601713 | 2009-05-26 14:43:04 | 2009-05-26 14:47:42 | 2009-05-26 14:53:05 | utf8_general_ci | NULL | | |
+-------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
SHOW VARIABLES LIKE 'key%';
+--------------------------+----------+
| Variable_name | Value |
+--------------------------+----------+
| key_buffer_size | 16777216 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
+--------------------------+----------+
SHOW VARIABLES LIKE 'innodb%';
+---------------------------------+---------------------------------+
| Variable_name | Value |
+---------------------------------+---------------------------------+
| innodb_additional_mem_pool_size | 2097152 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 16777216 |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | ibdata1:10M:autoextend:max:128M |
| innodb_data_home_dir | |
| innodb_adaptive_hash_index | ON |
| innodb_doublewrite | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_io_threads | 4 |
| innodb_file_per_table | ON |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | |
| innodb_force_recovery | 0 |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_arch_dir | |
| innodb_log_archive | OFF |
| innodb_log_buffer_size | 8388608 |
| innodb_log_file_size | 5242880 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 300 |
| innodb_rollback_on_timeout | OFF |
| innodb_support_xa | ON |
| innodb_sync_spin_loops | 20 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 8 |
| innodb_thread_sleep_delay | 10000 |
+---------------------------------+---------------------------------+
I tried your two queries. The first on didn't run any faster. The second one didn't run properly and gave an error near 'inner'.
I am indeed mostly interested in minute by minute data but I don't think a summary table would help much. These are organic connections I am querying against, so they have all different lengths and atypical start and end times. I am basically checking each row to see if some part of it happened during some portion of each minute interval over the day.
Any more suggestions would be appreciated