How to avoid full scans of table?

Sanvean · #1 (**permalink**) 07-19-04, 06:38

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?

iaguigon · #2 (**permalink**) 07-19-04, 06:55

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