List:General Discussion« Previous MessageNext Message »
From:supr_star Date:September 22 2011 6:44pm
Subject:query optimization
View as plain text  

 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