Re: I thisk I got it! It is possible, even *with* a subquery!
Sorry, I'm not that experienced in performance tuning. I can't be precise, but i can say some general things:
0) Start out with, say 1000 records, and verify that the query is in fact solving your problem. If not, we'd be tuning to no avail. This really should run for long.
1) Try the first (join) solution. Its bound to run faster than the second (subquery) one. to be sure, this:
delete log_table t1
from log_table t1
inner join log_table t2
on t1.member_name = t2.member_name
and t1.ip_address = t2.ip_address
and t1.running_time < t2.running_time
;
is the one i mean.
2) Do you have indexes? please post the table and index definitions. I'd say, you need an index over running_time, ip_address, member_name; in that order. Please don't pin my on this, I may be completely wrong here.
3) run
ANALYZE TABLE <tablename>
before performance testing
4) run
SHOW INDEX FROM tbl_name
and post the output here.
5) Unfortunately you cant run EXPLAIN for a DELETE statement. So i'm not sure if it would help, but run:
EXPLAIN
SELECT t1.member_name
, t1.ip_address
, t1.running_time
from log_table t1
inner join log_table t2
on t1.member_name = t2.member_name
and t1.ip_address = t2.ip_address
and t1.running_time < t2.running_time
and post the output here (that is, the statement that SELECTs the to be deleted rows)
If you post these, and i cannot help out, Im sure you could post to the performance forum (provide a link to this thread) and get help from one of the index and performance cracks (you know who you are ;-).