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 <suprstar1...@yahoo.com> 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) >
