SQL beschleunigen

[TheRealDarklord]

da bin ich wieder
heute mit dem nächsten Problem

Folgendes:
Für mein Browsergame benötige ich eine Karte. Deren Ausmaße sind zu Testzwecken bisher immer recht gering gewesen, aber auf den Spielserver soll diese Karte Ausmaße von 100*100 Sektoren erreichen können. Pro Sektor sind 10*10 Felder angesetzt.
Ich habe es der Rechenleistung zuliebe schon soweit dezimieren können, dass ich nur jede Zeile in einem Rutsch generiere. also zunächst 100 Sektoren und dann 100*100 Felder dazu. Das dauert so in etwa 1-2 Minuten. Das ist noch ok.
Beim Generieren werden durch verschieden Zufallswerte 6 verschiedene Typen erzeugt, die ein Feld in einem Sektor haben kann. Der Typ 6 (Dorf) ist für mein nächstes Script nötig. Nach dem Erzeugen der Karte folgt nämlich eine Bereinigung, die jedes Dorf überprüft, ob neben diesem Dorf noch eine weiteres existiert. Ist dies der Fall, so wird das aktuelle mit einem anderen Feld-Typ ersetzt.
Dieser Vorgang dauert schon etwas längern. Bei 100 Sektoren am Stück sind das ungefähr 2-4 Minuten pro Sektorenzeile.
Abschließend soll den verbliebenen Dörfern aus einer vorhandenen Tabelle mit 42.000 Dorfnamen per Zufall einer zugewiesen werden. Dieser bekommt dann in der DB das Flag used und wird für die weiteren Dörfer nicht mehr verwendet.
Wenn ich dieses Script anwerfe dann kann ich getrost Kaffee machen gehen, denn pro Sektorenzeile dauert das nicht unter 4 Minuten.

Rechnet man das nunmal selbst mit den Minimalzeiten zusammen, dann kommt man auf eine Minimalzeit von ca. 700 Minuten. Das will und kann ich nicht so belassen.

Ich kopier mal meine 3 Scripte hier rein und hoffe, man kann mir helfen, das alles zu beschleunigen.

Falls ich was vergessen haben sollte, dann fragt bitte nach

Karte generieren:

   (!$_SESSION[line])?$_SESSION[line]=1:$_SESSION[line]++;
   if(!$_SESSION[done]){
	if(!is_numeric($_POST[max_x]))
	 $_POST[max_x]=5;
	if(!is_numeric($_POST[max_y]))
	 $_POST[max_y]=5;
	$query->update_single_safe("config","value",$_POST[max_x],"WHERE name='max_x'");
	$query->update_single_safe("config","value",$_POST[max_y],"WHERE name='max_y'");
	$config[max_x]=$_POST[max_x];
	$config[max_y]=$_POST[max_y];
	$_SESSION[done]="ok";
   }
   mt_srand((double)microtime()*1000000);
   $food=35;
   $wood=65;
   $iron=75;
   $stone=85;
   $trade=90;
   for($x=1;$x<=$config[max_x];$x++){
	$query->insert("sectors",array("x","y"),array($x,$_SESSION[line]));
	echo mysql_error();
	for($yy=(($_SESSION[line]-1)*10)+1;$yy<=(($_SESSION[line]-1)*10)+10;$yy++){
	 for($xx=(($x-1)*10)+1;$xx<=(($x-1)*10)+10;$xx++){
	  $number=mt_rand(1,100);
	  if($number>=1&&$number<=$food)
	   $type=1;
	  elseif($number>$food&&$number<=$wood)
	   $type=2;
	  elseif($number>$wood&&$number<=$iron)
	   $type=3;
	  elseif($number>$iron&&$number<=$stone)
	   $type=4;
	  elseif($number>$stone&&$number<=$trade)
	   $type=5;
	  else
	   $type=6;
	  $query->insert("fields",array("x","y","type"),array($xx,$yy,$type));
	  echo mysql_error();
	 }
	}
   }
   $query->insert("admin_log",array("nick","action","ip","time"),array($_SESSION[nick],"Sektoren ".$_SESSION[line]."/".$config[max_y]." erzeugt.",$_SERVER[REMOTE_ADDR],time()));
   echo mysql_error();
   if($_SESSION[line]==$config[max_y]){
	$query->update_single_safe("config","value","1","WHERE name='map_ready'");
	$_SESSION[line]="";
	$_SESSION[done]="";
	echo"<a href=\"map.php\">Karte bereinigen</a>";
   }
   else
	echo"<a href=\"map.php?next=go\">Sektoren-Zeile ".($_SESSION[line]+1)."/".$config[max_y]." generieren</a>";

Karte bereinigen:

(!$_SESSION[clean])?$_SESSION[clean]=1:$_SESSION[clean]++;
mt_srand((double)microtime()*1000000);
$query->select("id,x,y","fields","USE KEY (primary) WHERE type='6' AND y BETWEEN '".((($_SESSION[clean]-1)*10)+1)."' AND '".((($_SESSION[clean]-1)*10)+10)."'","ORDER BY id ASC","");
for($i=0;$i<$query->line;$i++){
 $query->fetch_assoc($query->result);
 $subquery->select_fast("id","fields","USE KEY (primary) WHERE type='6' AND ((x='".($query->temp[x]+1)."' AND y='".$query->temp[y]."') OR (y='".($query->temp[y]+1)."' AND x='".$query->temp[x]."'))","","LIMIT 0,1");
 if($subquery->fetch_assoc($subquery->result)){
  $new=mt_rand(1,5);
  $subquery->free($subquery->result);
  $subquery->update_single_safe("fields","type",$new,"WHERE id='".$query->temp[id]."'");
 }
}
$query->insert("admin_log",array("nick","action","ip","time"),array($_SESSION[nick],"Sektoren ".$_SESSION[clean]."/".$config[max_y]." bereinigt.",$_SERVER[REMOTE_ADDR],time()));
if($_SESSION[clean]==$config[max_y]){
 $query->update_single_safe("config","value","1","WHERE name='map_clean'");
 echo"<a href=\"map.php\">D&ouml;rfer benennen</a>";
 $_SESSION[clean]="";
}

Dörfer benennen:

(!$_SESSION[name])?$_SESSION[name]=1:$_SESSION[name]++;
$query->select("id","fields","USE KEY (primary) WHERE type='6' AND y BETWEEN '".((($_SESSION[name]-1)*10)+1)."' AND '".((($_SESSION[name]-1)*10)+10)."'","","");
for($i=0;$i<$query->line;$i++){
 $query->fetch_assoc($query->result);
 $subquery->fetch_assoc($subquery->select_fast("name","city_names","USE KEY (primary) WHERE used='0'","","LIMIT 1"));
 $subquery->free($subquery->result);
 $subquery->insert("cities",array("id","name"),array($query->temp[id],$subquery->temp[name]));
 $subquery->update_single_safe("city_names","used","1","WHERE name='".$subquery->temp[name]."'");
}
$query->insert("admin_log",array("nick","action","ip","time"),array($_SESSION[nick],"Sektoren ".$_SESSION[name]."/".$config[max_y]." benannt.",$_SERVER[REMOTE_ADDR],time()));
if($_SESSION[name]==$config[max_y]){
 $query->update_single_safe("config","value","1","WHERE name='map_name'");
 echo"<a href=\"map.php\">Karte bearbeiten</a>";
 $_SESSION[name]="";
}
else
 echo"<a href=\"map.php\">Sektoren-Zeile ".($_SESSION[name]+1)."/".$config[max_y]." benennen</a>";

 

[patz]

Ich weiß nicht, ob es speziell in deinem Fall hilft, aber es hat meine Rechtschreibprüfung mit 500 000 Wörtern extrem beschleunigt (Text mit 1000 Worten statt in 20 Sekunden in 0.2 Sekunden geprüft!): Lege auf alle Spalten, die du abfragst, einen Index. Also z.B.

ALTER TABLE tabelle ADD INDEX(spalte)

Vielleicht hilft's ja auch in diesem Fall.

 

[TheRealDarklord]

Die Scripte 2 und 3 konnte ich erheblich beschleunigen.

Habe beim 2. ein JOIN mit sich selbst durchgeführt (wo ich in nem anderen Thread die Frage gestellt habe, wieso es net funktioniert) und dadurch mehrere Queries streichen können. Dann habe ich noch geringfügig die Struktur der DB geändert und wiederum Zeug streichen können.

Beim 3. Script habe ich durch das Hinzufügen eines Indexes in der Used-Spalte einen Geschwindigkeitsboost von fast 25 Sekunden bekommen (habe das vorhin gemacht, noch bevor ich lesen konnte, was du vorgeschlagen hattest - may be Telepathie? ).
Durch Hinzufügen eines LEFT JOINs und einer erneuten Änderung der DB-Struktur konnte ich den Source erheblich kürzen.

Schön, das es nun so klappt, wie ich will