Very slow delete on mysql base with subquery?
In my experience sub queries are often a cause of slow execution times in SQL statements, therefor I try to avoid them. Try this: DELETE tname FROM tname INNER JOIN spam ON (tname. Old_id = spam.
Textid) Disclaimer: This query is not tested, make backups first! :-).
In my experience sub queries are often a cause of slow execution times in SQL statements, therefor I try to avoid them. Try this: DELETE tname FROM tname INNER JOIN spam ON (tname. Old_id = spam.
Textid); Disclaimer: This query is not tested, make backups first! :-).
1 for using implicit SQL syntax, get out of 1989 and use explicit joins instead. Also this will not solve the problem as your assertion is untrue. OP needs to put indexes on the fields involved in the join.
– Johan Sep 9 at 12:06 s assertion's very much true for MySQL versions of that vintage. When they first introduced subqueries, and for some time after that, there were a plethora of performance issues with them. – Jon Bright Sep 9 at 12:07 +1, also make sure you have an index on spam.textid.
– nobody Sep 9 at 12:09 +1 back - this is a good query/statement. Test the execution plans to see if there is a difference... – Randy Sep 9 at 12:11 I have both are indexes. Thank you guys :) – rohezal Sep 9 at 12:11.
Your choice of where id in (select ...) will always perform poorly. Instead, use a normal join which will be very efficient: DELETE `text` FROM spam join `text` on `text`. Old_id = spam.
Textid; Notice selection from spam first, then joining to text, which will give the best performance.
Copy rows that are not in spam form text to new table. Then delete text table and rename created table. Good idea is not to add any keys to created table.
Add keys after renaming.
This mysql query is running since around 10 hours and is not finished. Something is horrible wrong. 2 tables (text and spam) are here.
Spam stores the ids of spam entrys in text that I want to delete. Spam has just 2 colums, both are ints. 800K entrys has a file size of several mbs.
I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.