making a query more simple ?

[Person]

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')";

[ToonMariner]

nowt wrong with that...

 

unless you have an old mysql version

[Person]

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

[ataria]

Do you echo anything?

or is your script just that query... ?

[Person]

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

[Barand]

In general, joins are faster than subqueries, which was why I requested the table structure in another topic of yours.

[ToonMariner]

I bet the query is still , < 1 second...

 

the problem may be elsewhere...

[Person]

even on php my admin... it takes about 3 mins

[ToonMariner]

hmmm that is a surpise even with the number of records...

[john010117]

Maybe you should put an index or a key in one of the fields...

[Barand]

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'