[SOLVED] Join on temporary table through php

[wsantos]

First I'd like to express my apologies to rjivgonzales ... this did work

 

mysql_query("DROP TEMPORARY TABLE IF EXISTS tmp_Main");
mysql_query("CREATE TEMPORARY TABLE tmp_Main SELECT src,uid FROM tblMain WHERE ... <my filters> ");

 

I did test it with

 

mysql_query("DROP TEMPORARY TABLE IF EXISTS tmp_Main");
mysql_query("CREATE TEMPORARY TABLE tmp_Main SELECT src,uid FROM tblMain WHERE ... <my filters> ");
$result = mysql_query("SELECT * FROM tmp_Main");
while($row=mysql_fetch_array($result,MYSQL_ASSOC))
  $daRes[] = $row;
print_r($daRes);

 

My bad for not testing by section.  It seemed I have the error on subsequent query which goes like

 

mysql_query("DROP TEMPORARY TABLE IF EXISTS tmp_Main");
mysql_query("CREATE TEMPORARY TABLE tmp_Main SELECT src,uid FROM tblMain WHERE ... <my filters> ");
mysql_query("DROP TEMPORARY TABLE IF EXISTS tmp_cid");
mysql_query("CREATE TEMPORARY TABLE tmp_Main SELECT src,uid FROM tblMain JOIN tmp_Main WHERE ... <my filters> ");
$result = mysql_query("SELECT * FROM tmp_cid");
while($row=mysql_fetch_array($result,MYSQL_ASSOC))
  $daRes[] = $row;
print_r($daRes);

 

Apparrently, I have an empty result on the join...which is not the case when I run the same query directly on mysql. Thanks for the help guys.

[BenInBlack]

I think you meant to name the second temp table name to be tmp_cid,but you named it tmp_main again

 

so it should be

mysql_query("DROP TEMPORARY TABLE IF EXISTS tmp_cid");
mysql_query("CREATE TEMPORARY TABLE tmp_cid SELECT src,uid FROM tblMain JOIN tmp_Main WHERE ... <my filters> ");

[wsantos]

Sorry on the typo...

 

This code still returned an empty result.

 

mysql_query("DROP TEMPORARY TABLE IF EXISTS tmp_Main");
mysql_query("CREATE TEMPORARY TABLE tmp_Main SELECT src,uid FROM tblMain WHERE ... <my filters> ");
mysql_query("DROP TEMPORARY TABLE IF EXISTS tmp_cid");
mysql_query("CREATE TEMPORARY TABLE tmp_cid SELECT src,uid FROM lookup_cid JOIN tmp_Main.uid=lookup_cid.uid ON tmp_Main WHERE ... <my filters> ");
$result = mysql_query("SELECT * FROM tmp_cid");
while($row=mysql_fetch_array($result,MYSQL_ASSOC))
  $daRes[] = $row;
print_r($daRes);

[BenInBlack]

can you please provide the full sql statements with the criteria, this is is working off of one table "tblMain" and I can't see any reason why the need for temp tables, until i see the full statements

[wsantos]

Sorry it was a typo on my end that's why it didn't work.  However using temporary table is our chosen approach though a join is an immediate obvious approach for a couple of reasons:

 

1.  That db is 60G records in 1 month for the main table alone which yeilds at least 10 records on the second table.  For each record on the second table at least 60 records on the third. For each record on the third at least 500 records on the 4th.

2.  Though that db is properly indexed there are too many stored procedures not including the individual queries of each of the ~500 workstations that is querying approximately every 1 min.

3.  That db is currently not replicated hence I cannot work on the mirrored box.

 

Any technical suggestions on this is highly appreciated.

[BenInBlack]

is this correct? "1.  That db is 60G" as in Gig as in 60 billion records in one month or the table size grows to 60G in a month?

[wsantos]

60 million records in one month