How To Optimize this code Code

[The Little Guy]

Is there any way that I could optimize this code even more? I have 1,309,345 rows in the table that it searches, and it is constantly is increasing.

 

$date = date("Y-m-d H:i:s", strtotime("now - 30 days"));
$sql2 = mysql_query(sprintf("SELECT COUNT(DISTINCT ip) as totalCount, referrence FROM stats WHERE 
owner_id = '%s' AND
project_id = '%s' AND
refDomain != '%s' AND
referrence != 'URL' AND
`date` >= '{$date}'
GROUP BY referrence ORDER BY totalCount DESC LIMIT 100",
mysql_real_escape_string($globalInfo['owner_id']),
mysql_real_escape_string($_GET['projectID']),
mysql_real_escape_string($domain)))or die(mysql_error());

[phant0m]

assuming these "Ids" are strings, just use integers instead. This should improve the performance.

 

and use an MySQL's index feature

 

also, you could use a separate table for the refDomain, and use a foreign key instead. This should improve the performance as well.

[The Little Guy]

I turned it into an unbuffered query instead of a normal mysql_query, and If I do this, I don't see any speed difference, but they say that mysql_unbuffered_query is faster...

 

while($row = mysql_fetch_array($sql2)){
$color = ($i%2) ? $color1 : $color2 ;
if($row['referrence'] == '')
	echo '<tr><td style="background-color:'.$color.';" class="lrgNum">'.$row['totalCount'].'</td><td style="background-color:'.$color.';" class="mid">Unknown</td></tr>';
else
	echo '<tr><td style="background-color:'.$color.';" class="lrgNum">'.$row['totalCount'].'</td><td style="background-color:'.$color.';" class="mid"><a href="'.$row['referrence'].'">'.substr($row['referrence'],0,75).'</a></td></tr>';
$i++;
if($i == 100){
	mysql_free_result($sql2);
	echo 'killed';
}
}

 

 

So, my question is: am I doing this correctly? Also... I am using integers in the database, but I place quotes around them in the query, does that slow it down any?

[Ken2k7]

Assign indexes.

[The Little Guy]

Assign indexes.

 

I have indexed everything I can.

 

Would it also be a good idea to spit the data into multiple tables, like phant0m suggested?

 

I have about 15 - 20 columns in this table.

[Ken2k7]

That depends. It has to make sense. You shouldn't blindly remove data from one place and put them elsewhere.

[The Little Guy]

could you explain more on that?

[phant0m]

well, if these references that you store are all different, it doesn't really make sense, because you would have around the same amount of rows. Looking for the id in there and then inserting it in your primary query would "double" the work.

But if you have rather few distinct references, it could run faster.