CODEWITHSUNDEEP
mysqlsql-delete
tttaaabbb
tttaaabbb

Reputation: 417

Increase execution time for MySql delete query

I need to remove duplicate rows in table by some value and leave only 1. i use this query

DELETE n1 FROM cities_extended n1, cities_extended n2 WHERE n1.city_id > n2.city_id AND n1.city = n2.city

but my table has about 65000 rows, and execution takes too long and i have no idea how to speed up this.

Upvotes: 0

Views: 365

Answers (1)

bsguy
bsguy

Reputation: 91

Making sure all of the fields in the where clause are indexed. If any of them are not indexed Running this query will index them.

ALTER TABLE cities_extended ADD INDEX `city` (`city`)

If this hasn't been indexed, it will definitely speed up the query.

You could also try using a temp table. 

drop table if exists `temp_for_duplicates`
CREATE TABLE `temp_for_duplicates` AS select * from `cities_extended` where 1 group by [field with duplicates]

truncate table `cities_extended`

INSERT INTO `cities_extended` SELECT * FROM `temp_for_duplicates`

Upvotes: 2

Related Questions