[SOLVED] Algorithm Help

[wsantos]

Guys just need your insights on a query algorigthm.  We have a very big database on a remote machine. From this database I need thre tables.  They are related in this way:

 

|Table 1|    |Table 2|    |Table 3|

|.....    |    |......    |    |.......  |

|Link  1|    |Link 1  |    |.......  |

|....      |    |Link 2  |    |Link 2  |

 

Using a join is out of the question since it crashes our network and our disk arrays. Hence, I got two options in mind:

 

1.  Use three queries with date filter and store it in different arrays then use the php to establish the relationships

2.  Use a main query at table 1 then use the link 1 as a filter for a query on a table 2 using for loop then use link 2 as a filter for a query on a table 3 using for loop.

 

I've done tests on a test server with local mysql data and seem no significant difference with respect to query speed and machine performance. However, I do not dare to implement it in production server before I get more insights as it might peg our network or worse lock the production db since it is way busier than the test server.

 

Thanks for your comments

[Barand]

 

Using a join is out of the question since it crashes our network and our disk arrays.

 

What are you doing?  Something like

SELECT * FROM table1, table2, table3

[wsantos]

Nah, the join version of these queries.

 

$qryCDR = "SELECT DISTINCT uniqueid,uid,clid,src,dst,origdst FROM cdr WHERE clid LIKE '%" . $strDisplay . "%' AND start BETWEEN '$starttime' AND
'$endtime'";
  $qryCP = "SELECT DISTINCT epoch,app,appdata,callid FROM callpath JOIN lookup_app ON lookup_app.appid=callpath.appid WHERE appexitstatus = -1 AND
from_unixtime(epoch) BETWEEN '$starttime' AND '$endtime'";
  $qryCID = "SELECT DISTINCT callid,uniqueid,uid from lookup_callid WHERE from_unixtime(substring_index(uniqueid,'.',1)) BETWEEN '$starttime' AND
'$endtime'";

 

there is just to many processes running on that db and with millions of records overhead join locks the db and we need a bucket of water for the disk arrays.  If a lock happens it mess up other systems as well.

[fenway]

there is just to many processes running on that db and with millions of records overhead join locks the db and we need a bucket of water for the disk arrays.  If a lock happens it mess up other systems as well.

I have no idea what this means.