CODEWITHSUNDEEP
mysqlsql
Bin
Bin

Reputation: 484

Always get error when I delete the same data in mysql

I have a table with about 1,000,000 rows, and I need to delete the same datas, so I use following SQL

delete
from history
where hid not in
(
    select hid from
    (
        select min(hid) as hid
        from history
        group by `invitedkey`, `userkey`, `charge`
    ) as b
);

Here hid is Primary, AUTO_INCREMENT, if the invitedkey, userkey, charge are the same, keep one data with minimum hid. but I run above SQL, I always get error

Lock wait timeout exceeded; try restarting transaction

And I googled, but there's no good answer, is there any ideas except increasing hardware configuration.

Upvotes: 1

Views: 80

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269743

Use join instead:

delete h
    from history h left join
         (select invitedkey, userkey, charge, min(hid) as min_hid
          from history h
          group by invitedkey, userkey, charge
         ) hh
         on hh.min_hid = hh.hid
    where hh.min_hid is null;

MySQL is not very good at optimizing update and delete queries. It is probably running the subquery once for every row, which is why you are getting a time out.

Upvotes: 0

Lihi
Lihi

Reputation: 19

I think your fifth line is unnecessary. The query in the brackets is enough to bring all the minimum hids.

Delete from history Where hid not in (select min(hid) from...)

.

Upvotes: 0

starko
starko

Reputation: 1149

In my opinion, it happens when the database is large and based on many transactions. Try to start with the following questions, though in the long run you will need to optimize the database

For example run this:

SET GLOBAL innodb_lock_wait_timeout = 4000; 
SET innodb_lock_wait_timeout = 4000;

Edit:

Very similar problem i found in THIS place

Upvotes: 1

Lihi
Lihi

Reputation: 19

I think your fifth line is unnecessary. The query in the brackets is enough to bring all the minimum bids.

Upvotes: 0

Related Questions