[Ilugc] Mysql query issue
- From: rajasuperman@xxxxxxxxx (Raja Subramanian)
- Date: Fri, 1 Apr 2011 18:43:00 +0530
On Fri, Apr 1, 2011 at 4:04 PM, JAGANADH G <jaganadhg at gmail.com> wrote:
I have a MySQL table and it contains around 20,000,00 records.
Your comas are misleading, how many records are you having?
20 million or 20 lakh records?
id(promary key and autogenerate),review(text),hash(int)) is the table
structure
There is a column called "hash",which has the hash value (generated
programatcally)
I am pretty sure that there is duplicate records in my table.Thats y i
generated a hash.
Now i would like to dedupe the table using hash.
To retain only the first duplicate record and delete all later ones, try this:
ALTER IGNORE TABLE your_table_name ADD PRIMARY KEY (hash);
This will create a primary key using your hash field, retain only the
first unique hash and delete everything else.
If this is too slow, the alternate method is to copy unique records
into a new table and delete the old table.
To view list of duplicates, run this query:
SELECT hash, COUNT(hash) AS duplicate_count FROM \
your_table_name GROUP BY hash HAVING (COUNT(hash) > 1);
Ensure you have created an index for the "hash" field before running
above query, this will help avoiding table scans and give you fast
performance.
Needless to say, backup your tables before bulk updating.
- Raja
Other related posts:
