Fixing unindexed database MySQL

Question

EDIT: I added a response with the link and command that solved my issue. To put it straight for any future readers desperate about their unindexed tables with duplicate rows, you'll find the answer bellow or in the link above.

I have a little screwed up table in my db. It was repeated row with the exact same information (including id).

Is there a query I can use to delete these repeated id's but one? I wanted to check my answer here first, if I delete this table it's going to take me the whole weekend to import it again (it's huge)...

I'm thinking of doing

DELETE FROM tickets t1, tickets t2 WHERE t1.id = t2.id limit 1;

I'm not sure this will work... Thanks!

How many rows did you duplicate? You could create a backup of your table and remove all the duplicate rows then add them again using the backup table. You should really use a unique primary key to avoid this problem. — Oceans, Oct 16 '15 at 09:38
It won't work if there is only one row with that id -- you will delete the one row. — Rick James, Nov 01 '15 at 04:26

score 2 · Answer 1 · edited May 23 '17 at 10:27

2

As Oceans says

CREATE TEMPORARY TABLE tmp_tickets AS SELECT DISTINCT * FROM tickets;
DELETE FROM tickets;
INSERT INTO tickets SELECT * FROM tmp_tickets;
DROP TABLE tmp_tickets;

edited May 23 '17 at 10:27

Community

1
1

answered Oct 16 '15 at 10:09

DimaS

571
2
5
19

That was my backup solution, thankfully I found an easier way to get accross this with a simple command. – Thaenor Oct 16 '15 at 10:14

score 1 · Answer 2 · answered Oct 16 '15 at 09:42

To find all the duplicates in the id column you could try:

select `id` 
   from `tickets`
   group by `id`
   having count(`id`)>1;

I would try this in a gui ( Heidi etc ) ~ you can then base your delete operations around this, either manually in the gui or by direct query.

score 0 · Answer 3 · edited May 23 '17 at 11:44

0

I managed to fix this with the solution found here Remove duplicate rows in MySQL. To save you clicking the link the query looked something like this

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

It removed all the repeated indexes, minus one.

edited May 23 '17 at 11:44

Community

1
1

answered Oct 16 '15 at 10:14

Thaenor

611
1
9
28

Fixing unindexed database MySQL

3 Answers3