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')"; Link to comment https://forums.phpfreaks.com/topic/49905-making-a-query-more-simple/ 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 Link to comment https://forums.phpfreaks.com/topic/49905-making-a-query-more-simple/#findComment-244846 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 Link to comment https://forums.phpfreaks.com/topic/49905-making-a-query-more-simple/#findComment-244849 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... ? Link to comment https://forums.phpfreaks.com/topic/49905-making-a-query-more-simple/#findComment-244851 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 Link to comment https://forums.phpfreaks.com/topic/49905-making-a-query-more-simple/#findComment-244856 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. Link to comment https://forums.phpfreaks.com/topic/49905-making-a-query-more-simple/#findComment-244859 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... Link to comment https://forums.phpfreaks.com/topic/49905-making-a-query-more-simple/#findComment-244861 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 Link to comment https://forums.phpfreaks.com/topic/49905-making-a-query-more-simple/#findComment-244867 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... Link to comment https://forums.phpfreaks.com/topic/49905-making-a-query-more-simple/#findComment-244873 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... Link to comment https://forums.phpfreaks.com/topic/49905-making-a-query-more-simple/#findComment-244886 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' Link to comment https://forums.phpfreaks.com/topic/49905-making-a-query-more-simple/#findComment-244955 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.