Announcement

Announcement Module
Collapse
No announcement yet.

Please help in increasing the speed of query

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Please help in increasing the speed of query



    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

  • #2
    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

    Comment


    • #3
      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

      Comment


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

        Comment


        • #5
          no while inserting into any table i m getting this error is the tables are corrpted

          Comment

          Working...
          X