query optimization

am 22.09.2011 20:44:29 von supr_star

Re: query optimization

am 22.09.2011 20:53:50 von Ananda Kumar

--20cf30563c37aa674704ad8c3695
Content-Type: text/plain; charset=ISO-8859-1

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 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)
>

--20cf30563c37aa674704ad8c3695--