Get similar sounding records

Asked Apr 10 '14 at 08:47

Active Apr 10 '14 at 09:37

Viewed 39 times

I want to find all duplicate names from contacts table whose name matches by sound. Eg : Rita or Reeta , Microsoft or Microsift , Mukherjee or Mukherji.

I have used below query :

select contacts.id from contacts INNER JOIN (SELECT first_name,last_name,count(*) as rows FROM contacts where deleted = 0 GROUP BY soundex(first_name),soundex(last_name) HAVING count(rows) > 1) as p where contacts.deleted=0 AND p.first_name sounds like contacts.first_name and p.last_name sounds like contacts.last_name ORDER BY contacts.date_entered DESC

The above query gives right results but takes a lot of time when there are many records.

edited Apr 10 '14 at 09:37

asked Apr 10 '14 at 08:47

user3286692

1

try Explain your query and post the result in the question. – Abhik Chakraborty Apr 10 '14 at 08:49
1

try caching the `soundex`ed first and lastname in additional columns and put indices on these – Marian Theisen Apr 10 '14 at 08:52
I doubt the query actually works, the order is `WHERE` and then `GROUP BY`.. – Daniel W. Apr 10 '14 at 08:52
1

This query is invalid. You left out the beginning when copying and pasting. – rsanchez Apr 10 '14 at 08:55
I am sorry , I had posted a wrong query earlier.Now have corrected it. – user3286692 Apr 10 '14 at 09:38

Get similar sounding records

0 Answers0