WP2X We can help your Wordpress faster 
Doing PHP project with MySQL database, I usually meet the problem with duplicate rows value. In the optimization process, I just want to delete all of rows except the first one. I see we have 2 options here:
- Fetch all of records to PHP and delete one by one if it is not the first one with the same value
- Execute one MySQL query to delete
The first way is easy and not a good way to do in big data. We will look at the second one what save time and server resource. We just need to execute these sql query
If you want to keep the row with the lowest id value
Let ‘s use one of these SQL
1 | DELETE n1 FROM names n1, names n2 WHERE n1.id > n2.id AND n1.name = n2.name |
or
1 2 3 4 5 | DELETE FROM NAMES WHERE id NOT IN (SELECT * FROM (SELECT MIN(n.id) FROM NAMES n GROUP BY n.name) x) |
If you want the id value that is the highest
1 | DELETE n1 FROM names n1, names n2 WHERE n1.id < n2.id AND n1.name = n2.name |
or
1 2 3 4 5 | DELETE FROM NAMES WHERE id NOT IN (SELECT * FROM (SELECT MIN(n.id) FROM NAMES n GROUP BY n.name) x) |
Now time to enjoy the result. Check it out and tell us if you meet any problem.
It solved my problem quite easy with this tip about mysql. I always do it in PHP but now, no need PHP code at all
It ‘s quite easy to delete and clean our database with your tips. That ‘s cool