Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2004
    Posts
    3

    Unanswered: How to avoid full scans of table?

    Hi!
    I have following table:
    Code:
    +----------------+---------------------+------+-----+---------------------+----------------+
    | Field          | Type                | Null | Key | Default             | Extra          |
    +----------------+---------------------+------+-----+---------------------+----------------+
    | id             | bigint(20) unsigned |      | PRI | NULL                | auto_increment |
    | ip_src         | char(15)            |      | PRI | 0                   |                |
    | ip_dst         | char(15)            |      | PRI | 0                   |                |
    | bytes          | bigint(20) unsigned |      | PRI | 0                   |                |
    | stamp_inserted | datetime            |      | PRI | 0000-00-00 00:00:00 |                |
    +----------------+---------------------+------+-----+---------------------+----------------+
    I have following indexes:
    Code:
    +-------+------------+----------+--------------+----------------+-----------+-------------+----------+--------+---------+
    | Table | Non_unique | Key_name | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Comment |
    +-------+------------+----------+--------------+----------------+-----------+-------------+----------+--------+---------+
    | data  |          0 | PRIMARY  |            1 | id             | A         |        NULL |     NULL | NULL   |         |
    | data  |          0 | PRIMARY  |            2 | ip_src         | A         |        NULL |     NULL | NULL   |         |
    | data  |          0 | PRIMARY  |            3 | ip_dst         | A         |        NULL |     NULL | NULL   |         |
    | data  |          0 | PRIMARY  |            4 | bytes          | A         |        NULL |     NULL | NULL   |         |
    | data  |          0 | PRIMARY  |            5 | stamp_inserted | A         |       95360 |     NULL | NULL   |         |
    | data  |          1 | ip_src   |            1 | ip_src         | A         |        4768 |     NULL | NULL   |         |
    | data  |          1 | ip_src   |            2 | ip_dst         | A         |       13622 |     NULL | NULL   |         |
    | data  |          1 | ip_src   |            3 | stamp_inserted | A         |       95360 |     NULL | NULL   |         |
    +-------+------------+----------+--------------+----------------+-----------+-------------+----------+--------+---------+
    Now, I run following query:

    SELECT ip_src, SUM(bytes) AS bytes FROM data USE INDEX(ip_src) WHERE (ip_src REGEXP '111\.12\.168\.(19[2-9]|2[01][0-9]|22[0-6])') AND (ip_dst NOT REGEXP '111\.12\.168\.(19[2-9]|2[01][0-9]|22[0-6])') AND (stamp_inserted BETWEEN '2004-7-10' AND '2004-7-19' AND 0<1) GROUP BY ip_src ORDER BY ip_src ASC;

    Query execution time is always 50 seconds, independent of time period. I guess, the cause of this behavior is incostistent index or wrong built query.
    Can you help me?

  2. #2
    Join Date
    May 2004
    Location
    Barcelona, Spain
    Posts
    54
    Quote Originally Posted by Sanvean
    SELECT ip_src, SUM(bytes) AS bytes FROM data USE INDEX(ip_src) WHERE (ip_src REGEXP '111\.12\.168\.(19[2-9]|2[01][0-9]|22[0-6])') AND (ip_dst NOT REGEXP '111\.12\.168\.(19[2-9]|2[01][0-9]|22[0-6])') AND (stamp_inserted BETWEEN '2004-7-10' AND '2004-7-19' AND 0<1) GROUP BY ip_src ORDER BY ip_src ASC;

    Query execution time is always 50 seconds, independent of time period. I guess, the cause of this behavior is incostistent index or wrong built query.
    Can you help me?
    I don't think an index will be used on a comparison with regexp.
    Try using <=, >= or between
    Hope this helps

    Regards

  3. #3
    Join Date
    Jul 2004
    Posts
    3
    Quote Originally Posted by iaguigon
    I don't think an index will be used on a comparison with regexp.
    Try using <=, >= or between
    Hope this helps
    Regards
    YESSS!!! It helps! Query ran 5 times faster.
    Thank you, iaguigon!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •