List:General Discussion« Previous MessageNext Message »
From:Ananda Kumar Date:September 22 2011 6:53pm
Subject:Re: query optimization
View as plain text  
Your outer query "select cpe_mac,max(r3_dt) from rad_r3cap", is doing a full
table scan, you might want to check on this and use a "WHERE" condition to
use indexed column

On Fri, Sep 23, 2011 at 12:14 AM, supr_star <suprstar1072@stripped> wrote:

>
>
>  I have a table with 24 million rows, I need to figure out how to optimize
> a query.  It has to do with mac addresses and radius packets - I want to see
> the # of connections and the min/max date. So I basically want all this
> data:
>
>   select cpe_mac,count(*) c,min(r3_dt) mindt,max(r3_dt) maxdt, max(rec_num)
> recn
>   from radiuscap
>   where r3_dt>=SUBDATE(NOW(),INTERVAL 30 DAY)
>     and r3_type='Access'
>   group by cpe_mac order by cpe_mac
> ;
>
> This piece of the query takes 30 seconds to run and produces 3500 rows.  I
> have r3_dt indexed.  I also want a status field of the row with the highest
> r3_dt:
>
> select rec_num,cpe_mac,req_status
> from rad_r3cap
> where r3_type='Access'
>   and (cpe_mac,r3_dt) in (select cpe_mac,max(r3_dt) from rad_r3cap)
> ;
>
> This piece of the query takes forever,  I let it run for an hour and it
> still didn't finish, it's obviously not using indexes.  I have no idea how
> far along it got.  I wrote a php script to run the 1st query, then do 3500
> individual lookups for the status using the max(rec_num) field in the 1st
> query, and I can get the data in 31 seconds.  So I CAN produce this data,
> but very slowly, and not in 1 sql query.  I want to consolidate this into 1
> sql so I can make a view.
>
> If anyone can point me in the right direction, I'd appreciate it!
>
>
>
> mysql> desc rad_r3cap;
> +-------------+-------------+------+-----+---------+----------------+
> | Field       | Type        | Null | Key | Default | Extra          |
> +-------------+-------------+------+-----+---------+----------------+
> | rec_num     | int(11)     | NO   | PRI | NULL    | auto_increment |
> | r3_dt       | datetime    | YES  | MUL | NULL    |                |
> | r3_micros   | int(11)     | YES  |     | NULL    |                |
> | r3_type     | varchar(16) | YES  |     | NULL    |                |
> | req_status  | varchar(16) | YES  |     | NULL    |                |
> | req_comment | varchar(64) | YES  |     | NULL    |                |
> | asn_ip      | varchar(16) | YES  | MUL | NULL    |                |
> | asn_name    | varchar(16) | YES  |     | NULL    |                |
> | bsid        | varchar(12) | YES  | MUL | NULL    |                |
> | cpe_ip      | varchar(16) | YES  |     | NULL    |                |
> | cpe_mac     | varchar(12) | YES  | MUL | NULL    |                |
> | filename    | varchar(32) | YES  |     | NULL    |                |
> | linenum     | int(11)     | YES  |     | NULL    |                |
> | r3_hour     | datetime    | YES  | MUL | NULL    |                |
> | user_name   | varchar(64) | YES  |     | NULL    |                |
> +-------------+-------------+------+-----+---------+----------------+
>
> mysql> show indexes in rad_r3cap;
>
>
> +-----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
> | Table     | Non_unique | Key_name     | Seq_in_index | Column_name |
> Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
>
>
> +-----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
> | rad_r3cap |          0 | PRIMARY      |            1 | rec_num     | A
>       |    23877677 |     NULL | NULL   |      | BTREE      |         |
> | rad_r3cap |          0 | r3cap_dt     |            1 | r3_dt       | A
>       |        NULL |     NULL | NULL   | YES  | BTREE      |         |
> | rad_r3cap |          0 | r3cap_dt     |            2 | r3_micros   | A
>       |        NULL |     NULL | NULL   | YES  | BTREE      |         |
> | rad_r3cap |          1 | r3cap_bsid   |            1 | bsid        | A
>       |         346 |     NULL | NULL   | YES  | BTREE      |         |
> | rad_r3cap |          1 | r3cap_asnip  |            1 | asn_ip      | A
>       |          55 |     NULL | NULL   | YES  | BTREE      |         |
> | rad_r3cap |          1 | r3cap_cpemac |            1 | cpe_mac     | A
>       |        4758 |     NULL | NULL   | YES  | BTREE      |         |
> | rad_r3cap |          1 | r3cap_date   |            1 | r3_hour     | A
>       |        1548 |     NULL | NULL   | YES  | BTREE      |         |
>
>
> +-----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
> 7 rows in set (0.00 sec)
>

Thread
query optimizationsupr_star22 Sep
  • Re: query optimizationAnanda Kumar22 Sep