How to compare arrays with best performance

I'm trying to compare the same table of two different databases with PHP/MySQL (looking for different values).

I already have the fields/values of each table on arrays:

DATABASE1.CONFIGTABLE

array(2) {
  [0]=>
  array(4) {
    ["id"]=>
    string(1) "1"
    ["path"]=>
    string(20) "some/key"
    ["value"]=>
    string(1) "Unique value"
  }
  [1]=>
  array(4) {
    ["id"]=>
    string(1) "2"
    ["path"]=>
    string(21) "some/url"
    ["value"]=>
    string(21) "http://domain1.com/"
  }
}

DATABASE2.CONFIGTABLE

array(2) {
  [0]=>
  array(4) {
    ["id"]=>
    string(1) "1"
    ["key"]=>
    string(20) "some/key"
    ["value"]=>
    string(1) "Unique value"
  }
  [1]=>
  array(4) {
    ["id"]=>
    string(1) "2"
    ["key"]=>
    string(21) "some/url"
    ["value"]=>
    string(21) "http://domain2.com/"
  }
}

I would like to compare them and return an array with only the values that are different. For example, if all values are the same ("id" and "key") but the "value" field is different, add it to the final array.

Since "Unique value" is the same for both tables but the domains are not, the desire array would be something like this:

array(1) {
  [0]=>
  array(4) {
    ["id"]=>
    string(1) "2"
    ["key"]=>
    string(21) "some/url""
    ["value"]=>
    string(21) "http://domain2.com/"
  }
}

Or even better:

array(1) {
  [0]=>
  array(4) {
    ["id"]=>
    string(1) "2"
    ["key"]=>
    string(21) "some/url""
    ["value_1"]=>
    string(21) "http://domain1.com/",
    ["value_2"]=>
    string(21) "http://domain2.com/"
  }
}

The idea is to look for the best way to do it performance-wise, since the tables can contain thousands of rows.

I hope it is clear, thanks in advance.

1 answer

  • answered 2019-09-15 20:48 GMB

    You can use a simple SQL query to identify unmatched records between both tables. Assuming that both tables have the same ids and paths, as shown in your sample data, you can find discrepencies in the value column with this query:

    SELECT
        t1.id,
        t1.path,
        t1.value value1,
        t2.value value2
    FROM database1.configtable t1
    INNR JOIN database2.configtable t2 
        ON t1.id = t2.id 
        AND t1.path = t2.path 
        AND t1.value != t2.value