| Please help in increasing the speed of query [message #1458] |
Fri, 22 June 2007 06:43  |
 clickshweta
Messages: 3
Registered: June 2007
Location: India |
Junior Member |
|
|
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 | <derived2> | 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
divine
|
|
|
|
| Re: Please help in increasing the speed of query [message #1460 is a reply to message #1458 ] |
Fri, 22 June 2007 07:54  |
clickshweta
Messages: 3
Registered: June 2007
Location: India |
Junior Member |
|
|
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
divine
|
|
|
|
| Re: Please help in increasing the speed of query [message #1462 is a reply to message #1461 ] |
Fri, 22 June 2007 08:40 |
clickshweta
Messages: 3
Registered: June 2007
Location: India |
Junior Member |
|
|
no while inserting into any table i m getting this error is the tables are corrpted
divine
|
|
|
Members
Search
Help
Register
Login
Home
