[SOLVED] HELP WITH QUERY

[dlebowski]

This query is taking about 4 seconds to run.  Can anyone help make it more efficient?  I have only a couple hundred rows in each table so it shouldn't take long at all. 

 

DELETE from sellertotals where SellerTotalNumber not in (select SellerNumber from lots WHERE LotAuctionDate = '$LotAuctionDate') AND SellerTotalDate = '$LotAuctionDate

 

This query removes any entry that has been made in the sellertotals table that doesn't match what is in the lots table for that same date. 

 

Any help would be appreciated.  Thanks.

[drewbee]

I remember a friend telling me he said using NOT IN made his queries run incredibly slow, but this was with an ACCESS database so it may be only relevant to that.

 

Personally for this I would just create two queries with one nested in the other.

[dlebowski]

I think I will do just that.  Thanks.

[rameshfaj]

DELETE from sellertotals where SellerTotalNumber not in (select SellerNumber from lots WHERE LotAuctionDate = '$LotAuctionDate') AND SellerTotalDate = '$LotAuctionDate

 

The query can be made simpler using an array to store the result of the inner query and then compare them with the result of the outer query.

[dlebowski]

DELETE from sellertotals where SellerTotalNumber not in (select SellerNumber from lots WHERE LotAuctionDate = '$LotAuctionDate') AND SellerTotalDate = '$LotAuctionDate

 

The query can be made simpler using an array to store the result of the inner query and then compare them with the result of the outer query.

 

Can someone help me with the "compare" part of the query.  I am struggling to see how I compare two results from two different queries.  Thanks for your help.

[dlebowski]

Can anyone give me an example of how I can get this code to work by not using "not in"?  I am really struggling to get this to work in a way that doesn't utilize "not in" and run efficiently.  Thanks in advance.

 

DELETE from sellertotals where SellerTotalNumber not in (select SellerNumber from lots WHERE LotAuctionDate = '$LotAuctionDate') AND SellerTotalDate = '$LotAuctionDate

[dlebowski]

I think I figured it out.  This is appears to work.  And VERY efficiently. 

 

$query = "SELECT SellerNumber FROM lots WHERE SellerNumber='$SellerTotalNumber' AND LotAuctionDate='$LotAuctionDate'";
$sellerresult = mysql_query($query) or die(mysql_error());
$sellertotalnumber=mysql_numrows($sellerresult);

mysql_close();

if ($sellertotalnumber<1)
{
include("dbinfo.inc.php");
mysql_connect("localhost",$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$query= "DELETE from sellertotals WHERE SellerTotalNumber='$SellerTotalNumber' AND SellerTotalDate='$LotAuctionDate'";
mysql_query($query);
mysql_close();

// echo "<BR><font size=1>$SellerTotalNumber Deleted!</font>";
}
else
{

}
}
$i++;