MySQL Forums
Forum List  »  Newbie

Re: I thisk I got it! It is possible, even *with* a subquery!
Posted by: Roland Bouman
Date: July 27, 2005 09:22AM

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 ;-).

Options: ReplyQuote


Subject
Written By
Posted
Re: I thisk I got it! It is possible, even *with* a subquery!
July 27, 2005 09:22AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.