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)
