CODEWITHSUNDEEP
mysql
Lucas Matos
Lucas Matos

Reputation: 1162

How can i make this heavy SQL query lighter?

Sometimes i use this sql query to remove duplicated posts from my posts table. But its takes a long time to finish, letting my domain unaccessible for like 5 minutes. Look like this is a heavy sql for the server, theres a way to make it lighter and faster?

DELETE FROM hotaru_posts WHERE post_id NOT IN ( SELECT a.post_id FROM ( SELECT post_title, post_id FROM hotaru_posts GROUP BY post_title ) a )

The optimal and fastest query for this is 

delete from hotaru_posts where post_id in (
  select post_id from (
    select post_id from hotaru_posts a group by post_title having count(post_title) > 1
  ) b
)

This query took only 0.0603 sec. while the fisrt query took more than 5 min. (lol)

Upvotes: 0

Views: 2982

Answers (2)

Lucas Matos
Lucas Matos

Reputation: 1162

The optimal and fastest query for this is

delete from hotaru_posts where post_id in (
  select post_id from (
    select post_id from hotaru_posts a group by post_title having count(post_title) > 1
  ) b
)

This query took only 0.0603 sec. while the fisrt query took more than 5 min. (lol)

Upvotes: 0

Travesty3
Travesty3

Reputation: 14479

Would this work?

DELETE FROM hotaru_posts
WHERE post_id IN (
    SELECT post_id
    FROM (
        SELECT MAX(post_id)
        FROM hotaru_posts a
        GROUP BY post_title
        HAVING COUNT(post_title) > 1
    ) b
)

Upvotes: 1

Related Questions