Please help in increasing the speed of query

:frowning:

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

select * from argus where argus.start_time >‘2007-06-13 19:00:00’ and argus.start_time <‘2007-06-14 20:00:00’

In the above query if you could have an index on the start_time it would speed the query

yes i have created the index u can check in explain stateements??? now i m getting an error

ERROR 1030 (HY000): Got error -1 from storage engine
do u know whts the problem??

thanks

when are u getting this error ie at the time of creating the index??