On Tuesday, March 15, 2016, Andreas Kretschmer <
andreas@a-kretschmer.de> wrote:
>
> I am trying to delete the connections with date 2016-03-10 by using the
> following:
>
>
> delete from connection where uid in (select uid from connection where ts >
> '2016-03-10 00:30:00');
try to rewrite that to :
delete from connection where ts > '2016-03-10 00:30:00';
It's simpler - and (maybe) faster.
It also gives a different answer...
> There are around 800.000 records matching this rule, and seems to be taking
> an awful lot of time - 4 hours and counting. What could be the reason for
> such a performance hit and how could I optimise this for future cases?
>
> Regards.
the db has to touch such many rows, and has to write the transaction log. And
update every index. And it has to check the referenced tables for the
constraints. Do you have proper indexes?
Given the lack of indexes on the one table that is shown I suspect this is the most likely cause (FK + indexes)
David J.