php/mysql: geschwindigkeitsproblem

Code:

Feld  	Typ   	Null  	Standard   	Verweise  	Kommentare  	MIME
id  	mediumint(8) 	Nein  	  	  	  	 
uid  	int(10) 	Nein  	0  	  	  	 
name  	varchar(50) 	Nein  	  	  	  	 
account  	varchar(15) 	Nein  	  	  	  	 
gilde  	varchar(50) 	Ja  	NULL  	  	  	 
klasse  	varchar(15) 	Ja  	NULL  	  	  	 
rasse  	varchar(10) 	Ja  	NULL  	  	  	 
lehen  	varchar(50) 	Ja  	NULL  	  	  	 
last_online  	int(10) 	Nein  	0  	  	  	 
sum_online  	mediumint(8) 	Nein  	1  	  	  	 

 Indizes :
Name  	Typ  	Kardinalität  	Feld
id 	UNIQUE 	660 	id
uid 	INDEX 	660 	uid

Code:

Feld  	Typ   	Null  	Standard   	Verweise  	Kommentare  	MIME
charid  	mediumint(8) 	Nein  	0  	chars -> id  	  	 
zeit  	int(10) 	Nein  	0  	  	  	 
x  	smallint(5) 	Nein  	0  	  	  	 
y  	smallint(5) 	Nein  	0  	  	  	 


 Indizes :
Name 	Typ 	Kardinalität 	Feld
zeit 	INDEX 	15317 	zeit
charid 	INDEX 	850 	charid

Code: PHP

$time_start = getmicrotime();
$query = 'SELECT gilde AS name, count(*) AS mitglieder FROM chars WHERE last_online>' . $temp . ' AND klasse<>\'\' GROUP BY gilde ORDER BY gilde ASC';
$result = mysql_query($query) OR die(mysql_error());

for ($gild_summe = $i = 0; $i < mysql_num_rows($result); $i++) {
	$gilden[$i] = mysql_fetch_assoc($result);
	$gild_summe += $gilden[$i]['mitglieder'];
	if (strlen($gilden[$i]['name']) < 2) {
		$gilden[$i]['name'] = 'gildenlos';
	}
}

for ($i = 0; $i < count($gilden); $i++) {
	$temp = ($gilden[$i]['mitglieder']/$gild_summe)*100;
	$gilden[$i]['proz'] = round($temp, '2');
	unset($temp);
}

for ($i=0; isset($gilden[$i]['name']);$i++) {
	if ($gilden[$i]['name'] != 'gildenlos') {
		$query1 = 'SELECT id FROM chars WHERE gilde=\'' . mysql_escape_string($gilden[$i]['name']) . '\'';
		$result1 = mysql_query($query1);
		$temp = $time-60*60*24*30;
		$zeile1 = mysql_fetch_assoc($result1);
		$query2 = 'SELECT charid, count(*) AS online FROM cstatus WHERE zeit>' . $temp . ' AND (charid=\'' . $zeile1['id'] . '\'';
		while ($zeile1 = mysql_fetch_assoc($result1)) {
			$query2 .= ' OR charid=\'' . $zeile1['id'] .'\'';
		}
		unset($temp);
		$query2 .= ') GROUP BY charid';
		$result2 = mysql_query($query2);
		$gilden[$i]['online'] = 0;
		while ($zeile2 = mysql_fetch_assoc($result2)) {
			$gilden[$i]['online'] += $zeile2['online'];
		}
		$gilden[$i]['onlineprochar'] = $gilden[$i]['online'] / $gilden[$i]['mitglieder'];
		$gilden[$i]['onlineprochar'] = round($gilden[$i]['onlineprochar']);
		unset($temp);
	}
	else {
		$gilden[$i]['onlineprochar'] = $gilden[$i]['online'] = '-';
	}
}
$time_end = getmicrotime();
$time_used = $time_end - $time_start;
echo "$time_used<br>\n\n";

Code: PHP

$time_start = getmicrotime();

$query = 'SELECT chars.gilde AS name, COUNT(DISTINCT chars.name) AS mitglieder, COUNT(cstatus.charid) AS online FROM chars LEFT JOIN cstatus ON chars.id=cstatus.charid WHERE cstatus.zeit>' . $temp . ' AND chars.klasse<>\'\' GROUP BY chars.gilde ORDER BY chars.gilde ASC';
$result = mysql_query($query) OR die(mysql_error());
for ($i=0;$i<mysql_num_rows($result); $i++) {
	$zeile[$i] = mysql_fetch_assoc($result);
	$zeile[$i][onlineprochar] = $zeile[$i][online]/$zeile[$i][mitglieder];
	$zeile[$i][onlineprochar] = round($zeile[$i][onlineprochar]);
}

$time_end = getmicrotime();
$time_used = $time_end - $time_start;
echo "$time_used\n\n";

Code: PHP

// Query: (Hoffe, ich hab mich mit den funktionen nicht vertan...)
SELECT
	chars.gilde AS name,
	COUNT(DISTINCT chars.name) AS mitglieder,
	COUNT(cstatus.charid) AS online
	ROUND(online/mitglieder) as onlineprochar
FROM chars
LEFT JOIN cstatus
	ON chars.id=cstatus.charid
WHERE
	cstatus.zeit>' . $temp . '
	AND chars.klasse<>''
GROUP BY chars.gilde
ORDER BY chars.gilde ASC

// Loop:
while($zeile[] = mysql_fetch_assoc($result)) {
    ;
}