Challenging MySQL query, compare two records to find if they match or not

Question

I have a large (88K rows) table that holds data in pairs.

For a specific entity, there are 2 records, with UID's and then a load of additional data.

I need to take each pair (pairs can be found by matching another ID number) and compare the additional data.

So, I'd find a pair, compare the data and if either of the data values is the same (or different) flag that pair (and allow me to potentially run an update on one of the rows)

I can script this out n my PHP application if it requires multiple queries, but if this can be done just in SQL, that'd be great.

Any updates to a specific row would just be change a certain value to 0.

Table:

CREATE TABLE `ad_transitions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `asset_id` int(11) NOT NULL,
  `left` varchar(50) DEFAULT '0',
  `top` varchar(50) DEFAULT '0',
  `width` varchar(50) DEFAULT '0',
  `height` varchar(50) DEFAULT '0',
  `boundingWidth` varchar(50) DEFAULT NULL,
  `boundingHeight` varchar(50) DEFAULT NULL,
  `fill` varchar(50) DEFAULT '0',
  `shadow` varchar(128) DEFAULT NULL,
  `filter` varchar(512) DEFAULT NULL,
  `overlayFill` varchar(50) DEFAULT NULL,
  `opacity` varchar(50) DEFAULT '1',
  `scaleX` varchar(50) DEFAULT '1',
  `scaleY` varchar(50) DEFAULT '1',
  `angle` varchar(50) DEFAULT '0',
  `wait` int(11) DEFAULT '0',
  `duration` int(11) DEFAULT '0',
  `easing` varchar(50) DEFAULT 'Linear.easeNone',
  `originX` varchar(128) DEFAULT 'center',
  `originY` varchar(128) DEFAULT 'center',
  PRIMARY KEY (`id`)
)

Data:

INSERT INTO `database`.`ad_transitions` (`id`, `asset_id`, `left`, `top`, `width`, `height`, `boundingWidth`, `boundingHeight`, `fill`, `shadow`, `filter`, `overlayFill`, `opacity`, `scaleX`, `scaleY`, `angle`, `wait`, `duration`, `easing`, `originX`, `originY`) VALUES ('497', '161', '0', '0', '1280', '720', NULL, NULL, '#000000', NULL, NULL, NULL, '0', '1', '1', '0', '0', '0', 'Linear.easeNone', 'left', 'top');
INSERT INTO `database`.`ad_transitions` (`id`, `asset_id`, `left`, `top`, `width`, `height`, `boundingWidth`, `boundingHeight`, `fill`, `shadow`, `filter`, `overlayFill`, `opacity`, `scaleX`, `scaleY`, `angle`, `wait`, `duration`, `easing`, `originX`, `originY`) VALUES ('498', '161', '0', '0', '1280', '720', NULL, NULL, '#000000', NULL, NULL, NULL, '1', '1', '1', '0', '0', '5000', 'Linear.easeNone', 'left', 'top');
INSERT INTO `database`.`ad_transitions` (`id`, `asset_id`, `left`, `top`, `width`, `height`, `boundingWidth`, `boundingHeight`, `fill`, `shadow`, `filter`, `overlayFill`, `opacity`, `scaleX`, `scaleY`, `angle`, `wait`, `duration`, `easing`, `originX`, `originY`) VALUES ('499', '162', '269.16666666667', '-104', '986', '104', NULL, NULL, '#000000', NULL, NULL, NULL, '1', '1', '1', '0', '0', '0', 'Linear.easeNone', 'left', 'top');
INSERT INTO `database`.`ad_transitions` (`id`, `asset_id`, `left`, `top`, `width`, `height`, `boundingWidth`, `boundingHeight`, `fill`, `shadow`, `filter`, `overlayFill`, `opacity`, `scaleX`, `scaleY`, `angle`, `wait`, `duration`, `easing`, `originX`, `originY`) VALUES ('500', '162', '269.16666666667', '24', '986', '104', NULL, NULL, '#000000', NULL, NULL, NULL, '1', '1', '1', '0', '5000', '1000', 'Bounce.easeOut', 'left', 'top');

Answer 1

You can do this in SQL and make it very efficient at the same time. Essentially what we'll do is join the table to itself on your two Ids. Once you have a full set of all matching IDs you can compare the additional field values. To do this efficiently you can build them out into a single string, and hash it, then compare the hashes. It is exceptionally efficient and effective. Abbreviated example below.

SELECT
     id
    ,asset_id
FROM
(
 SELECT
     id
    ,asset_id
    ,SHA1(left + '|' + top) As DataCompare
    FROM ad_transitions
) As FirstSet
JOIN
(
 SELECT
     id
    ,asset_id
    ,SHA1(left + '|' + top) As DataCompare
    FROM ad_transitions
) As SecondSet
    ON FirstSet.id = SecondSet.id
    AND FirstSet.asset_id = SecondSet.asset_id
WHERE FirstSet.DataCompare = SecondSet.DataCompare

The pipe is added to the end of each field string (convert where necessary) to prevent issues that could result from odd but always possible data patterns such as the following:

Name     RandomText
Joh      Normand
John     Ormand

If just these two items are hashed, as is, they will be identical, despite they clear fact they are very different. The addition of a pipe after the field fixes johnnormand to joh|normand and john|ormand ensuring a unique hash and thus no accidental duplicate detection.