Pruning large mysql table?

Question

What is the most efficient way to prune a mysql table of about 40,000 rows. What i would like to do is remove rows with the same title.

The only way i can think to do this is to look at each title (all 40,00 of them) then search for that title in the rest of the table (seems very inefficient), so i was hoping someone knows of a better way.

Sorry, maybe i wasn't clear. I know how to delete a row, but finding the rows to delete is the part i'm not sure about. Like i said, only way i can think is move through each row, and search for it in the rest of the database, very inefficient.

Answer 1

I'd do it by creating a new table with a unique index on the title and then insert into it. Assuming your table was named "articles" with the column "title" you could do something like:

create table unique_articles like articles;
alter table unique_articles add unique index (title);
insert ignore into unique_articles select * from articles;

The ignore keyword will discard rows that violate the unique index on title. You can then perform a drop and rename:

drop table articles;
rename table unique_articles to articles;

This information should be valid for 4.1 or later. If you are using an earlier version you will need to check the documentation.

Answer 2

An SQL statement like:
DELETE From tablename WHERE titlecol = "criteria"

If you're trying to compare data in the table to just remove duplicates you'll have to put a conditional statement in the WHERE portion of the SQL query... or sometimes it may be easier to create a SELECT statement to find the matching data, create a temp table, etc.

Here's more details on the Delete statement that may help:
http://dev.mysql.com/doc/refman/5.0/en/delete.html