Remove duplicate rows in MySQL

Question

I have a table with the following fields:

id (Unique)

url (Unique)

title

company

site_id

Now, I need to remove rows having same title, company and site_id. One way to do it will be using the following SQL along with a script (PHP):

SELECT title, site_id, location, id, count( * ) 

FROM jobs

GROUP BY site_id, company, title, location

HAVING count( * ) >1

After running this query, I can remove duplicates using a server side script.

But, I want to know if this can be done only using SQL query.

Answer 1

You can add the UNIQUE index on the 3 columns.

Note: When you use the ALTER statement then you need to include IGNORE keyword.

Query:

ALTER IGNORE TABLE jobs

ADD UNIQUE INDEX idx_name (Site_Id, title, company);

This will help to drop all the duplicate rows. Additional benefit of using INSERT is duplicates will be removed.

You can refer to this video to understand DROP in detail.