Person Posted May 3, 2007 Share Posted May 3, 2007 is there anyway to make this query more simple ? SELECT SUM(1) AS clicks, SUM(`clcpc`), SUM(`chcpc`) FROM `nuke_pnAffiliate_clicktracking` WHERE `pl` = 'rpu' AND `date` > '20070228'AND `cl` IN (SELECT DISTINCT(cl) FROM rpu_sales WHERE salesman = 'ryan')"; Quote Link to comment Share on other sites More sharing options...
ToonMariner Posted May 3, 2007 Share Posted May 3, 2007 nowt wrong with that... unless you have an old mysql version Quote Link to comment Share on other sites More sharing options...
Person Posted May 3, 2007 Author Share Posted May 3, 2007 I can get it to give me the info i need... i can get the info when i do it in php my admin... but when i run my code it takes long and nothing is echo'ed. There are 13 mil lines in the DB Quote Link to comment Share on other sites More sharing options...
ataria Posted May 3, 2007 Share Posted May 3, 2007 Do you echo anything? or is your script just that query... ? Quote Link to comment Share on other sites More sharing options...
Person Posted May 3, 2007 Author Share Posted May 3, 2007 heres the code <?php $host = "localhost"; $user = ""; $pass = ""; $dbname = ""; $con = mysql_connect($host,$user,$pass) or die (mysql_errno().": ".mysql_error()."<BR>"); mysql_select_db($dbname); $query = "SELECT SUM(1) AS clicks, SUM(`clcpc`), SUM(`chcpc`) FROM `nuke_pnAffiliate_clicktracking` WHERE `pl` = 'rpu' AND `date` > '20070228'AND `cl` IN (SELECT DISTINCT(cl) FROM rpu_sales WHERE salesman = 'ryan')"; $result = mysql_query($query); $num_results = mysql_num_rows($result); $row = mysql_fetch_assoc($result); mysql_free_result($result); echo $result; ?> It brings up Resource id #5 where the myadmin brings up clicks SUM(`clcpc`) SUM(`chcpc`) 87343 3423.2699509561 12342.159823611 Quote Link to comment Share on other sites More sharing options...
Barand Posted May 3, 2007 Share Posted May 3, 2007 In general, joins are faster than subqueries, which was why I requested the table structure in another topic of yours. Quote Link to comment Share on other sites More sharing options...
ToonMariner Posted May 3, 2007 Share Posted May 3, 2007 I bet the query is still , < 1 second... the problem may be elsewhere... Quote Link to comment Share on other sites More sharing options...
Person Posted May 3, 2007 Author Share Posted May 3, 2007 even on php my admin... it takes about 3 mins Quote Link to comment Share on other sites More sharing options...
ToonMariner Posted May 3, 2007 Share Posted May 3, 2007 hmmm that is a surpise even with the number of records... Quote Link to comment Share on other sites More sharing options...
john010117 Posted May 3, 2007 Share Posted May 3, 2007 Maybe you should put an index or a key in one of the fields... Quote Link to comment Share on other sites More sharing options...
Barand Posted May 4, 2007 Share Posted May 4, 2007 Does this work OK and is it quicker? SELECT COUNT(a.*) AS clicks, SUM(a.clcpc), SUM(a.chcpc) FROM `nuke_pnAffiliate_clicktracking` a INNER JOIN rpo_sales s ON a.cl = s.cl WHERE a.`pl` = 'rpu' AND a.`date` > '20070228' AND s.salesman = 'ryan' Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.