query efficiency

[supr_star]

Here's a simple query:

select name,status,min(dt), max(dt),count(*)
from mytable
group by name

I need an efficient way to get this data, along with the status of the row with 
the max(dt).  'status' is not in the group by, so I get apparently random 
statuses.  Is there any way to do this without a table join?  Or I guess the 
real question is:  What is the most efficient way of accomplishing this?  The 
table is huge, 25M rows or so, but I can create any indexes.

query optimization

[supr_star]

 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)

very large import

[supr_star]

I recently recovered a very large corrupt database with your help.  Now I'm 
having a problem during the next step, importing all the data back into a clean 
system.  I have a 24GB dump file, the ibdata1 file was about 100GB when the 
disk filled and everything got wrecked.  I have a server with a lot more drive 
space now, and the import is working, albeit very very slowly  The ibdata1 
file is growing at about 800MB-1GB per hour.  It's been running for 24 hours 
now, and it's only 1/4 of the way thru!   Is there any way to speed up this 
process? by disabling indexes or something?  I can't afford to be down for 3 
more days...  Thanks again,

doug

corrupt innodb database

[supr_star]

I'm not sure if this is the correct way to post, my apologies if it's not..   
Anyway, I have a zabbix system on a mysql database where the ibdata1 file grew 
to 93GB and filled up the disk.  Restarting mysql results in:

/etc/init.d/mysql: ERROR: The partition with /var/lib/mysql is too full!


This db is on its own partition, so I can't delete logs or anything else to 
clear up space.  So I moved ib_logfile0 and ib_logfile1 to /tmp to clear 10 
megs, and restart.  mysql started but everything is out of sync now.  ERROR 
1033 (HY000): Incorrect information in file: './zabbix/users.frm' and similar 
for any tables I try to desc, select, etc.

I put 

[mysqld]
innodb_force_recovery = 4

and tried every number from 1-8 in /etc/mysql/my.cnf.  I just took this job and 
there is no backup of this database.  Any command I issue in mysql results in:

ERROR 1033 (HY000): Incorrect information in file: './zabbix/users.frm'

for any table in zabbix.

mysql repair table users;
+--++--+-+
| Table        | Op     | Msg_type | Msg_text                                   
         |
+--++--+-+
| zabbix.users | repair | Error    | Incorrect information in file: 
'./zabbix/users.frm' |
| zabbix.users | repair | error    | Corrupt                                    
         |
+--++--+-+
2 rows in set (0.01 sec)

mysql

Is this 'game over' or can this data be recovered in any way?

Thanks,