Deleting Duplicates in MySQL

Question

Query was this:

CREATE TABLE `query` (
`id` int(11) NOT NULL auto_increment,
`searchquery` varchar(255) NOT NULL default '',
`datetime` int(11) NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM

first I want to drop the table with:

ALTER TABLE `querynew` DROP `id`

and then delete the double entries.. I tried it with:

INSERT INTO `querynew` SELECT DISTINCT * FROM `query`

but with no success.. :(

and with ALTER TABLE query ADD UNIQUE ( searchquery ) - is it possible to save the queries only one time?

Answer 1

You should be able to do it without first removing the column:

DELETE FROM `query`
 WHERE `id` IN (
        SELECT `id`
          FROM `query` q
         WHERE EXISTS ( -- Any matching rows with a lower id?
                SELECT *
                  FROM `query`
                 WHERE `searchquery` = q.`searchquery`
                   AND `datetime` = q.`datetime`
                   AND `id` < q.`id`
               )
       );

You could also go via a temp table:

SELECT MIN(`id`), `searchquery`, `datetime`
  INTO `temp_query`
 GROUP BY `searchquery`, `datetime`;

DELETE FROM `query`;

INSERT INTO `query` SELECT * FROM `temp_query`;

Answer 2

I would use MySQL's multi-table delete syntax:

DELETE q2 FROM query q1 JOIN query q2 USING (searchquery, datetime)
WHERE q1.id < q2.id;

Answer 3

I would do this using an index with the MySQL-specific IGNORE keyword. This kills two birds with one stone: it deletes duplicate rows, and adds a unique index so that you will not get any more of them. It is usually faster than the other methods as well:

alter ignore table query add unique index(searchquery, datetime);