MySql Optimisation

[Anon]

Okie dokily..

Im using the script
<PRE>
$select_distinct = mysql_query("SELECT DISTINCT uid FROM ".$PANTS[s_site]."uid_trans");
$select_distinct_rows = mysql_num_rows($select_distinct);

for ($i=0; $i<$select_distinct_rows; $i++)
{
	$uid = mysql_result($select_distinct, $i, 'uid');
	$get_n_name = mysql_query("SELECT n_name,uid FROM ".$PANTS[s_site]."user WHERE uid='$uid'");
	$n_name = @mysql_result($get_n_name, 0, 'n_name');
	$get_cred_info = mysql_query("SELECT credit_value,uid FROM ".$PANTS[s_site]."uid_trans WHERE uid='$uid'");
	$get_cred_info_rowd = mysql_num_rows($get_cred_info);

	for ($a=0; $a<$get_cred_info_rowd; $a++)
	{
		$cred_result = mysql_result($get_cred_info, $a, 'credit_value');
		$credit_value = $credit_value + $cred_result;
	}
if($credit_value >= $PANTS[quan])
{
	echo "$uid : $credit_value : $n_name<br>";
}
$credit_value = '0';
}

</PRE>

The table that its contained within is ~ 6000 rows .. now this query is taking an age to complete .. anything I can change to speed it up ?

Thanks for your help...

Carl

[[deleted]]

Looks like you are doing JOINs the (very)hard way.

If I'm correct, all you're doing is selecting the unique users from uid_trans, then finding the name for each user from 'user', and then printing adding all the credit_value for that user.

Try this something like this from the MySQL commandline:

SELECT user.uid,
user.name,
SUM(uid_trans.credit_value)
FROM user, uid_trans
WHERE user.uid = uid_trans.uid
GROUP BY user.name
ORDER BY user.name ASC

I suggest you open the MYSQL manual pages and just flick through the pages, look at the command and what they basically do. Just to give you an idea of what SQL can do.

---

A forum, a FAQ, what else do you need?

Time to YAPF!: http://www.hvt-automation.nl/yapf/

[Anon]

Cheers for that.. damn good bit of sql and has reduced my code drastically...

Got any good recomendations for mysql books .. not combined mysql/php but purest mysql..

About time I improved my long winded mysql skills

Ta!

Carl

[[deleted]]

Forget books, they are expensive and outdated before they are printed. Use Google to find tutorials instead.

MySQL is so very limited that you should get more than enough info just by reading the manual and the comments on it (but don't believe everything they write about performance, MySQL lies about that quite often)

http://www.mysql.com/doc/J/O/JOIN.html

[Anon]

ok thnx.. last million dollar question.. how do I return the result for SUM(uid_trans.credit_value) ..

I usually use
mysql_result($result_var, $i,'fieldname');

but cant seem to get it to work for the SUM field..

Thanks again...

Carl

[[deleted]]

Ah, sorry about that, should have told you rightaway.

you can give alternative names to fields using the AS operator:

SELECT SUM(field) AS yournamehere FROM table;

how you can get the data under the name 'yournamehere'