Hi i m using Innodb as default engine and my tables are not much heavy each table contains 70,000 rows but my performance os a query is very slow. My server RAM is 1 Gb and my innodb confifuration are follows
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 500M
innodb_file_io_threads = 4M
innodb_log_buffer_size = 8M
my qeury that is taking long time is as follows
select distinct(start_time), res.src_addr, res.dst_addr, res.src_port, res.dst_port, res.protocol, p0f.os, res.src_pkt, res.dst_pkt, res.src_bytes,res.dst_bytes from (select * from argus where argus.start_time >‘2007-06-13 19:00:00’ and argus.start_time <‘2007-06-14 20:00:00’) res left join p0f on res.start_time=p0f.p0f_timestamp left join snort on res.start_time=alert_timestamp ;
Actually i m going to take the data from three tables argus,p0f and snort. between the particular timestamp. here is my explain query
±—±------------±-----------±-----±--------------±— —±--------±---------------±------±-------------------- --+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±-----------±-----±--------------±— —±--------±---------------±------±-------------------- --+
| 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 3814 | Using temporary |
| 1 | PRIMARY | p0f | ref | time1 | time1 | 9 | res.start_time | 1 | |
| 1 | PRIMARY | snort | ref | time1 | time1 | 9 | res.start_time | 1 | Using index; Distinct |
| 2 | DERIVED | argus | ALL | st_time | NULL | NULL | NULL | 78480 | Using where |
±—±------------±-----------±-----±--------------±— —±--------±---------------±------±-------------------- --+
please tell me any solution as my data is going to increase alot in few days… and my this query is very important.
thanks
shweta