Timing Out - Compare 2 tables

[eman neercs]

I've got 2 tables, basically this weeks stock list and last weeks. Each has around 25,000 SKU's. I'm trying to run a comparison programme to see where there are stock-outs i.e. SKU missing from the latest table. Table structures identical. Both about 20mb in size.

Script is timing out so wondered if there is a way to avoid this

<?php {foreach (glob('test.txt') as $fn) {unlink($fn);}}?>
<?php 
include 'db_dave.php';
$query = "SELECT con_inv_old.SKU
FROM con_inv_old
LEFT JOIN con_inv
ON con_inv_old.SKU = con_inv.SKU
WHERE con_inv.SKU IS NULL";
$result = mysql_query($query) or die (mysql_error() . "<br />Couldn't execute query: $query");
$num = mysql_num_rows($result);
$i = 0;
while ($i < $num) { 
$EAN = mysql_result($result, $i, "con_inv_old.SKU");
$File = "test.txt";
$Handle = fopen($File, 'a');

$Data = $EAN;
fwrite($Handle, "$Data\r\n");

fclose($Handle);
 $i++;
}
mysql_close();
?>

[pritaeas]

SKU is indexed I assume.

You can try this to see if it makes a difference (it avoids a costly join):

SELECT `SKU`
FROM `con_inv_old`
WHERE `SKU` NOT IN (
    SELECT `SKU`
    FROM `con_inv`
    WHERE `SKU` IS NOT NULL
)

[jstfsklh211]

ahouldn't even need the where on the inner query

[pritaeas]

No, but it reduces the result set.

[eman neercs]

Thanks for the code.

This works after I had reduced the table size - not enough processing power.

SELECT b.SKU
FROM con_inv_old b
WHERE b.SKU NOT IN (
    SELECT a.SKU
    FROM a.con_inv
    WHERE b.SKU IS NOT NULL
)

The list gives me those in "b" (old data) that are not in "a" (new data). I now want to use the list of those not in "a" to select data from "b" but it is timing out again.

SELECT b.SKU, a.SKU, a.quantity
    FROM con_inv_old b
    WHERE b.SKU NOT IN (
        SELECT a.SKU
        FROM a.con_inv
        WHERE b.SKU IS NOT NULL
    )

Or is my code wrong.

[jstfsklh211]

what data are you trying to select
probably what you want is

SELECT SKU, quantity
FROM con_inv_old
WHERE SKU NOT IN (
    SELECT SKU FROM con_inv
)

this should give you the sku and quantity for each item missing from con_inv

@pritaeas
the only the only null sku's would have been the ones from the outer join
without the outer join the where is not null should be extra