Connection

[rseigel]

I have the following code:

mysql_connect("localhost", "username", "password") or die(mysql_error());
mysql_select_db("database") or die(mysql_error());
error_reporting(E_ALL);
ini_set('display_errors', 1);

mysql_query("DROP TABLE IF EXISTS tmp_discontinued")
    or die(mysql_error());
mysql_query("CREATE TABLE IF NOT EXISTS tmp_discontinued (
    supplier_reference varchar(32) DEFAULT NULL
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8")
    or die(mysql_error());

$result = mysql_query("INSERT INTO tmp_discontinued (supplier_reference) SELECT product_supplier_reference FROM product_supplier")
    or die(mysql_error());

$result2 = mysql_query('SELECT * FROM tmp_discontinued WHERE tmp_discontinued.supplier_reference NOT IN (SELECT supplier_reference FROM tmp_price_compare) ORDER BY tmp_discontinued.supplier_reference')
    or die(mysql_error());
    
echo "DISCONTINUED PRODUCTS<br /><br />";
while($row = mysql_fetch_array($result))
  {
    echo "{$row['supplier_reference']}<br />";
  }
echo "<br /><br />";

The table temp_discontinued is created and populated. No problems there.

 

The issue is that I get the following error:

Lost connection to MySQL server during query

This table has around 20,000 records.

 

Does anyone see any way to optimize this so I won't get the lost connection?

 

Thanks

[requinix]

What does an EXPLAIN of it return? I'm guessing tmp_price_compare doesn't have any indexes either?

 

And why are you using a not-actually-temporary temporary table for this?

[rseigel]

The other table didn't have indexes.

 

I did this:

mysql_query("DROP TABLE IF EXISTS tmp_price_compare")
	or die(mysql_error());
mysql_query("CREATE TABLE IF NOT EXISTS tmp_price_compare (
	supplier_reference varchar(32) DEFAULT NULL,
	quantity int(10) NOT NULL DEFAULT '0',
	price decimal(20,6) NOT NULL DEFAULT '0.000000',
	wholesale_price decimal(20,6) NOT NULL DEFAULT '0.000000'
	) ENGINE=InnoDB  DEFAULT CHARSET=utf8")
	or die(mysql_error());
mysql_query("CREATE INDEX indx_tmp ON tmp_price_compare (supplier_reference)")
	or die(mysql_error());

and BOOM. The SELECT is blazing fast.

 

tmp_price_compare is in another script that runs once an hour through a cron.

 

Thanks for the tip.