More efficient method to check for duplicate database entries?

Question

I am saving company information in a database, and if repeat information is found, I want to update the overlapping rows. Some of the supported fields are emails, phone numbers, organization names, and latitude/longitude positions. One of the obvious issues I've had to deal with, however, is knowing whether or not an organization is a duplicate of an already saved one. Consequently, I came up with some basic combinations of unique information that distinguishes one company from another:

1. phone number + organization_names
2. organization_names + location
3. email hash + website hash
4. phone + email hash
5. phone + website hash
6. email hash + location
7. email hash + organization name
8. website hash + location
9. website hash + organization_name

My workflow, as of now is as follows:

1. Determine if any of the above unique combinations are satisfied by the new information
2. Loop through each combination and create an MySQL query to check the database for duplicate entries

3. Use PHP's multi_query to run semicolon separated select statements for the above queries. These queries look like this:

   SELECT id, jaro_winkler_similarity(normalized, "ORGANIZATION_NAME") AS organization_name_similarity0 FROM profiles LEFT JOIN phones USING(id) LEFT JOIN (SELECT id, normalized FROM organization_names WHERE normalized LIKE "%ORGANIZATION%" OR normalized LIKE "%NAME%"  AS likeMatches USING(id) WHERE (number="6316870100" OR number="123") HAVING organization_name_similarity0 > 85;
   SELECT id, jaro_winkler_similarity(normalized, "ORGANIZATION_NAME") AS organization_name_similarity0 FROM profiles LEFT JOIN (SELECT id, normalized FROM organization_names WHERE normalized LIKE "%ORGANIZATION%" OR normalized LIKE "%NAME%"  AS likeMatches USING(id) LEFT JOIN locations USING(id) WHERE normalized_house_number="110" AND normalized_street_name="WASHINGTON" AND zip="00501" HAVING organization_name_similarity0 > 85;
   SELECT id, jaro_winkler_similarity(normalized, "ORGANIZATION_NAME") AS organization_name_similarity0, (3959 * acos(cos(radians(40.82127)) * cos(radians(latitude)) * cos(radians(longitude) - radians(-73.051872)) + sin (radians(40.82127)) * sin(radians(latitude)))) AS coordinate_distance0 FROM profiles LEFT JOIN (SELECT id, normalized FROM organization_names WHERE normalized LIKE "%ORGANIZATION%" OR normalized LIKE "%NAME%"  AS likeMatches USING(id) LEFT JOIN locations USING(id) HAVING organization_name_similarity0 > 85 AND coordinate_distance0 < 0.1;
   SELECT id FROM profiles LEFT JOIN emails USING(id) LEFT JOIN websites USING(id) WHERE (hash="93e6b0eff1d85b8b177fc44ce66cd1871dcb89b8") AND (hash="433833d572dc47f8576468a384ef8539a15f4031");
   SELECT id FROM profiles LEFT JOIN phones USING(id) LEFT JOIN emails USING(id) WHERE (number="6316870100" OR number="123") AND (hash="93e6b0eff1d85b8b177fc44ce66cd1871dcb89b8");
   SELECT id FROM profiles LEFT JOIN phones USING(id) LEFT JOIN websites USING(id) WHERE (number="6316870100" OR number="123") AND (hash="433833d572dc47f8576468a384ef8539a15f4031");
   SELECT id FROM profiles LEFT JOIN emails USING(id) LEFT JOIN locations USING(id) WHERE (hash="93e6b0eff1d85b8b177fc44ce66cd1871dcb89b8") AND normalized_house_number="110" AND normalized_street_name="WASHINGTON" AND zip="00501";
   SELECT id, (3959 * acos(cos(radians(40.82127)) * cos(radians(latitude)) * cos(radians(longitude) - radians(-73.051872)) + sin (radians(40.82127)) * sin(radians(latitude)))) AS coordinate_distance0 FROM profiles LEFT JOIN emails USING(id) LEFT JOIN locations USING(id) WHERE (hash="93e6b0eff1d85b8b177fc44ce66cd1871dcb89b8") HAVING coordinate_distance0 < 0.1;
   SELECT id, jaro_winkler_similarity(normalized, "ORGANIZATION_NAME") AS organization_name_similarity0 FROM profiles LEFT JOIN emails USING(id) LEFT JOIN (SELECT id, normalized FROM organization_names WHERE normalized LIKE "%ORGANIZATION%" OR normalized LIKE "%NAME%" OR normalized LIKE "%firm%" OR normalized LIKE "%pc%") AS likeMatches USING(id) WHERE (hash="93e6b0eff1d85b8b177fc44ce66cd1871dcb89b8") HAVING organization_name_similarity0 > 85;
   SELECT id FROM profiles LEFT JOIN websites USING(id) LEFT JOIN locations USING(id) WHERE (hash="433833d572dc47f8576468a384ef8539a15f4031") AND normalized_house_number="110" AND normalized_street_name="WASHINGTON" AND zip="00501";
   SELECT id, (3959 * acos(cos(radians(40.82127)) * cos(radians(latitude)) * cos(radians(longitude) - radians(-73.051872)) + sin (radians(40.82127)) * sin(radians(latitude)))) AS coordinate_distance0 FROM profiles LEFT JOIN websites USING(id) LEFT JOIN locations USING(id) WHERE (hash="433833d572dc47f8576468a384ef8539a15f4031") HAVING coordinate_distance0 < 0.1;
   SELECT id, jaro_winkler_similarity(normalized, "ORGANIZATION_NAME") AS organization_name_similarity0 FROM profiles LEFT JOIN websites USING(id) LEFT JOIN (SELECT id, normalized FROM organization_names WHERE normalized LIKE "%ORGANIZATION%" OR normalized LIKE "%NAME%"  AS likeMatches USING(id) WHERE (hash="433833d572dc47f8576468a384ef8539a15f4031") HAVING organization_name_similarity0 > 85;
   

4. If a match is found, grab the associated company id and add new information

5. If no matches are found, add a new company id and add the new information to the database

This, to me, seems incredibly superfluous but more importantly is slow. I'd like to condense my MySQL queries (I'm slightly ashamed by them, there has to be a better way to do this) into something more succinct, but I can't think of an appropriate way to do so. Similarly, I can't break if a match is found or a match isn't found, because data might be saved using a different unique combination which needs to be checked as well.

Answer 1

I would enforce uniqueness through unique indexes in the database table itself, not through making and evaluating a series of SELECT statements.

This allow you to GREATLY simplify your UPSERT operation to a single query. That might look something like.

/* Assume for this example there is a unique index on field_a and field_b */
INSERT INTO table (field_a, field_b, field_c) VALUES (1, 2, 3)
ON DUPLICATE KEY UPDATE field_c = 3

Now, the challenge here is that if your table has multiple unique indexes, you can get some unexpected behavior. So you probably need to reconsider your uniqueness criteria. Why would you check uniqueness of hashes of field combinations? This makes NO sense. Think in REAL WORLD terms about what makes a company listing unique. This probably doesn't include things like links to one or more websites on a related website table, or one or more phone numbers on a related phone number table, or one or more emails on a related email table, etc. All the fields you need to determine uniqueness of a company should all exist on the company table, so you can place a single unique index across them.