Re: Fast way to delete big table?

On Mon, May 16, 2016 at 10:22 PM, hmzha2 <Haiming(dot)Zhang(at)redflex(dot)com(dot)au> wrote:

> Works ok in my testing environment but not
> on the production machine. Select * from tableA limit 1; takes milliseconds
> to finish update summary table from the previous select result, takes
> milliseconds delete from tableA where primaryKey = ... takes 2.9 hours to
> finish.

Perhaps you have a foreign constraint pointing to your tableA from
another table, and the other table is un-indexed on those columns (in
production). So every delete from tableA leads to a full scan of that
other table to make sure no offending rows exist.

If that is not the case, then I'd try to do a "strace -ttt -T -y -p
<PID>" on the process which is taking so long, and see what it is
doing. Unfortunately, you have to do that on production, as that is
the only system where the problem appears.

Cheers,

Jeff