MySQL supprimer les doublons de grande base de données rapide

Question

J'ai une grande (>Mil lignes) de base de données MySQL foiré par des doublons. Je pense qu'il pourrait être de 1/4 à 1/2 de l'ensemble de la db remplie avec eux. J'ai besoin de se débarrasser d'eux rapidement (je veux dire l'exécution de la requête du temps). Voici à quoi il ressemble:
id (index) | texte1 | texte2 | texte3
texte1 et texte2 combinaison doit être unique, s'il y a des doublons, une seule combinaison avec texte3 PAS NULL doit rester. Exemple:

1 | abc | def | NULL  
2 | abc | def | ghi  
3 | abc | def | jkl  
4 | aaa | bbb | NULL  
5 | aaa | bbb | NULL

...devient:

1 | abc | def | ghi   #(doesn't realy matter id:2 or id:3 survives)   
2 | aaa | bbb | NULL  #(if there's no NOT NULL text3, NULL will do)

Les nouveaux identifiants froid être n'importe quoi, ils ne dépendent pas de la vieille id de table.
J'ai essayé des choses comme:

CREATE TABLE tmp SELECT text1, text2, text3
FROM my_tbl;
GROUP BY text1, text2;
DROP TABLE my_tbl;
ALTER TABLE tmp RENAME TO my_tbl;

Ou SELECT DISTINCT et d'autres variations.
Pendant qu'ils travaillent sur de petites bases de données, l'exécution de requêtes temps sur la mienne est juste énorme (jamais eu à la fin, en fait; > 20 min)

Est-il un moyen plus rapide de faire cela? Merci de m'aider à résoudre ce problème.

Answer 1

Je crois que ça va le faire, grâce à une clé en double + ifnull():

create table tmp like yourtable;

alter table tmp add unique (text1, text2);

insert into tmp select * from yourtable 
    on duplicate key update text3=ifnull(text3, values(text3));

rename table yourtable to deleteme, tmp to yourtable;

drop table deleteme;

Devrait être beaucoup plus rapide que tout ce qui nécessite group by ou distinct ou une sous-requête, ou même commander par. Cela ne nécessite même pas d'un filesort, qui va tuer les performances sur une grande table temporaire. Aura toujours besoin d'une analyse complète sur la table d'origine, mais il n'y a pas de moyen d'éviter cela.

Answer 2

Trouvé ce simple 1 ligne de code pour faire exactement ce dont j'avais besoin:

ALTER IGNORE TABLE dupTest ADD UNIQUE INDEX(a,b);

Tiré de: http://mediakey.dk/~cc/mysql-supprimer-double-entrées/

Answer 3

DELETE FROM dups
WHERE id NOT IN(
    SELECT id FROM (
        SELECT DISTINCT id, text1, text2
            FROM dups
        GROUP BY text1, text2
        ORDER BY text3 DESC
    ) as tmp
)

Cette requêtes de tous les dossiers, des groupes par la distinction des champs et des commandes par ID (signifie que nous choisissons le premier pas null texte3 record). Ensuite, sélectionnez l'id de résultat (ce sont de bonnes id...elles ne seront pas supprimés) et de supprimer tous les codes qui NE sont PAS ceux-là.

Toute requête de ce touchant l'ensemble de la table sera lente. Vous avez juste besoin de le lancer et le laisser rouler de sorte que vous pouvez les éviter dans le futur.

Après avoir fait cette "solution", je voudrais appliquer un INDEX UNIQUE (texte1, texte2) à la table. Pour éviter la possibilité de doublons dans l'avenir.

Si vous voulez aller à la section "créer une nouvelle table et remplacement de l'ancienne" route. Vous pouvez utiliser le très intérieure instruction select pour créer votre instruction insert.

MySQL spécifiques (prend une nouvelle table est nommée my_tbl2 et a exactement la même structure):

INSERT INTO my_tbl2
    SELECT DISTINCT id, text1, text2, text3
            FROM dups
        GROUP BY text1, text2
        ORDER BY text3 DESC

Voir MySQL INSERT ... SELECT pour plus d'informations.

Answer 4

supprimer les doublons sans supprimer les clés étrangères

create table tmp like mytable;
ALTER TABLE tmp ADD UNIQUE INDEX(text1, text2, text3, text4, text5, text6);
insert IGNORE into tmp select * from mytable;
delete from mytable where id not in ( select id from tmp);

Answer 5

Si vous pouvez créer une nouvelle table, le faire avec une clé unique sur le texte1 + texte2 champs. Ensuite l'insérer dans la table ignorer les erreurs (à l'aide de l'INSERT IGNORER la syntaxe):

select * from my_tbl order by text3 desc

• Je pense que la commande par texte3 desc va mettre les valeurs Null dernier, mais assurez-vous bien que.

L'index sur tous ces colonnes pourrait aider beaucoup, mais la création d'eux pouvait être assez lent.