How to Remove Duplicate Rows in MySQL Using a Unique Index?

Remove Duplicate Rows in MySQL Using an Index

To eliminate duplicate rows based on specific columns in a MySQL table, a unique index can be created. An index is a data structure that improves query performance by organizing data in a specific order.

Step 1: Create a Unique Index

To create a unique index on the columns that define unique rows (e.g., title, company, and site_id), use the following SQL statement:

ALTER IGNORE TABLE jobs
ADD UNIQUE INDEX idx_name (site_id, title, company);

How it Works

The IGNORE keyword in the SQL statement instructs MySQL to ignore any duplicate rows when creating the index. This means that only the first occurrence of each unique combination of values will be included in the index.

Result

After creating the index, all duplicate rows will be automatically removed from the table. This ensures that the table contains only unique rows based on the specified columns.

Note: This technique is no longer supported in MySQL versions 5.7 and above.

The above is the detailed content of How to Remove Duplicate Rows in MySQL Using a Unique Index?. For more information, please follow other related articles on the PHP Chinese website!