MySql Abfrage deutschlich schneller machen!

Thomas_Jung

Erfahrenes Mitglied
Hallo

Folgendes Script funktiniert genau wie ich möche. :)

Ziel ist es.
Die Lagerverwaltung auszulesen (ca 30.000 Datensätze).
Datensätze in der Lagerverwaltung

Lager 2
Regal 1
Hoehe 1
Fach 1

Lager 2
Regal 1
Hoehe 1
Fach 2

Lager 2
Regal 1
Hoehe 1
Fach 3

u.s.w bis Fach 30 und fängt dann in der 2 Hohe wieder an bis in die 9 Hoehe.
Das gleiche mit Regal 2,3,4 u.s.w

In der Datenbank nach den Datesätzen zu suchen die den gleichen Lagerort (lager,regal,hoehe,fach) besitzen (Anzahl).
Herrausfinden wieviel meter (im gleichen Lagerort lager,regal,hoehe,fach) vergeben wurde (die Summe).

PHP:
	$sql = "SELECT lager, regal, hoehe, fach FROM weilhammer_lager_2 LIMIT 1000";
//	$sql = "SELECT lager, regal, hoehe, fach FROM weilhammer_lager_2 WHERE lager = '2' AND regal = '1' AND hoehe = '1' AND fach = '1'";
	echo "<br /><code>".$sql."</code><br />";	
	$sql_result = mysql_query($sql) or die ("<b>Ein Fehler ist aufgetreten.</b><br /><b>Query:</b> <pre>" . $sql . "</pre><br /> <b>Error:</b> (" . mysql_errno() . ") <br /><pre>" . mysql_error() . "</pre><br /><b>Datei:</b> "  . __FILE__ . "<br /><b>Zeile:</b> " . __LINE__ );
	
	while($row = mysql_fetch_array($sql_result)) {
    $lager = $row['lager'];
	$regal = $row['regal'];
	$hoehe = $row['hoehe'];
	$fach = $row['fach'];


//ABFRAGE ANZAHL DATENSÄTZE
	$sql_2 = "SELECT lager, regal, hoehe, fach  FROM weilhammer_datenbank WHERE lager = '$lager' AND  regal = '$regal' AND hoehe = '$hoehe' AND fach = '$fach'  ";
	echo "<br /><code>".$sql_2."</code><br />";		
	$sql_2_result = mysql_query($sql_2) or die ("<b>Ein Fehler ist aufgetreten.</b><br /><b>Query:</b> <pre>" . $sql_2 . "</pre><br /> <b>Error:</b> (" . mysql_errno() . ") <br /><pre>" . mysql_error() . "</pre><br /><b>Datei:</b> "  . __FILE__ . "<br /><b>Zeile:</b> " . __LINE__ );
	$anzahl = mysql_num_rows($sql_2_result);
	if($anzahl > 0) {
	echo "Es sind ".$anzahl." Datensätze vorhanden.<br>";
	} else {
	echo "Es sind keine Datensätze vorhanden.<br>";
	}  

//ABFRAGE ANZAHL METER	
	$sql_3 = "SELECT SUM(meter) FROM weilhammer_datenbank WHERE lager = '$lager' AND  regal = '$regal' AND hoehe = '$hoehe' AND fach = '$fach'";
	echo "<br /><code>".$sql_3."</code><br />";	
	$sql_3_result = mysql_query($sql_3)  or die ("<b>Ein Fehler ist aufgetreten.</b><br /><b>Query:</b> <pre>" . $sql_3 . "</pre><br /> <b>Error:</b> (" . mysql_errno() . ") <br /><pre>" . mysql_error() . "</pre><br /><b>Datei:</b> "  . __FILE__ . "<br /><b>Zeile:</b> " . __LINE__ );
	$count = mysql_result($sql_3_result,0);
	if ($count > 0) {
	echo "Es sind ".$count." Meter vorhanden.<br>";
	} else {
	echo "Es sind keine Meter vorhanden.<br>";
	}  	
	}


PROBLEM
Eine Abfrage mit
SELECT lager, regal, hoehe, fach FROM weilhammer_lager_2 LIMIT 1000
dauert Seitenaufbau in 38 Minuten und 55 Sekunden.
Das geht doch mit Sicherheit auch schneller.:) ODER.:(

Gruß Thomas
 
Warum löst du das nicht alles in einer Abfrage?
SQL:
SELECT lager, regal, hoehe, fach, COUNT(*) as anzahl, SUM(meter) as meter FROM weilhammer_datenbank GROUP BY lager, regal, hoehe, fach
 
HI einfach nur crack

Danke für deine Antwort.

Vorher
Seitenaufbau in 38 Minuten und 55 Sekunden.

Nachher
Seitenaufbau in 19 Minuten und 20 Sekunden.

Satte 19 Minuten schneller.

Geht es noch schneller oder besser?

Tabellen synchronisieren?

Gruß Thomas
 
HI
Hier ist die neue Version

$sql = "SELECT lager, regal, hoehe, fach FROM weilhammer_lager_2 LIMIT 1000";
// $sql = "SELECT lager, regal, hoehe, fach FROM weilhammer_lager_2 WHERE lager = '2' AND regal = '1' AND hoehe = '1' AND fach = '1'";
echo "<br /><code>".$sql."</code><br />";
$sql_result = mysql_query($sql) or die ("<b>Ein Fehler ist aufgetreten.</b><br /><b>Query:</b> <pre>" . $sql . "</pre><br /> <b>Error:</b> (" . mysql_errno() . ") <br /><pre>" . mysql_error() . "</pre><br /><b>Datei:</b> " . __FILE__ . "<br /><b>Zeile:</b> " . __LINE__ );

while($row = mysql_fetch_array($sql_result)) {
$lager = $row['lager'];
$regal = $row['regal'];
$hoehe = $row['hoehe'];
$fach = $row['fach'];

$sql_3 = "SELECT lager, regal, hoehe, fach, COUNT(*) AS anzahl, SUM(meter) AS meter FROM weilhammer_datenbank WHERE lager = '$lager' AND regal = '$regal' AND hoehe = '$hoehe' AND fach = '$fach' GROUP BY lager, regal, hoehe, fach";
echo "<br /><code>".$sql_3."</code><br />";
$sql_3_result = mysql_query($sql_3) or die ("<b>Ein Fehler ist aufgetreten.</b><br /><b>Query:</b> <pre>" . $sql_3 . "</pre><br /> <b>Error:</b> (" . mysql_errno() . ") <br /><pre>" . mysql_error() . "</pre><br /><b>Datei:</b> " . __FILE__ . "<br /><b>Zeile:</b> " . __LINE__ );
//$row = mysql_fetch_array($sql_3_result);
$myrow = mysql_fetch_array($sql_3_result, MYSQL_ASSOC);

if($myrow['anzahl'] > 0) {
echo "Es sind ".$myrow['anzahl']." Datensätze vorhanden.<br>";
} else {
echo "Es sind keine Datensätze vorhanden.<br>";
}
if ($myrow['meter'] > 0) {
echo "Es sind ".$myrow['meter']." Meter vorhanden.<br>";
} else {
echo "Es sind keine Meter vorhanden.<br>";
}


}

Gruß Thomas
 
Zuletzt bearbeitet:
Für was brauchst du bitte die erste Abfrage noch einmal? Wenn es Datensätze gibt, die doppelt vorhanden sind, dann siehst du das ganz einfach an der virtuellen Spalte anzahl!
 
HI einfach nur crack

In der Datenbank gibt es Datensätze (Aktenordner) die immer einen Lagerort haben.
Z.B Lager 10 - Regal 100- Höhe 5 - Fach 20.
Nun möchte ich wissen wieviele Datensätze (Aktenordner) befinden sich den im
Lager 10 - Regal 100- Höhe 5 - Fach 20.
Daher die Anzahl der Datensätze.

Sobald es einen Datensatz (Aktenordner) im Lager 10 - Regal 100- Höhe 5 - Fach 20 gibt,
wir ein Meter ind er Datenbank vergeben.
(Es darf aber nur 1 Meter pro Fach vergeben werden.)
Daher die Meteranzahl.

Wenn es keine Datensätze in der Datenbank mit Lager 10 - Regal 100- Höhe 5 - Fach 20 gibt.
Weiss man das, das Fach leer ist.

Sind Datensätze vorhanden aber es existiert kein Meter im Lager 10 - Regal 100- Höhe 5 - Fach 20
bezahlt der Kunde nichts. (Weil nach Meter abgerechnet wird.)

Gruß Thomas
 
Hallo Thomas

Was willst du denn überhaupt wissen? Und vor allem was willst du ausgeben? Denn ich denke nicht dass du 1000 mal hintereinander ausgeben willst "Es sind Datensätze vorhanden ...."

Ich denke du hast mehrere Probleme:

1. Zuviele Abfragen: du führst für jeden der 1000 zeilen eine neue Abfrage durch. Auch wenn jede nur 100ms dauert bist du bei 100 sec. (was auch schon viel ist)

Diese Abfrage:
SQL:
select l.lager, l.regal, l.hoehe, l.fach
	, count(*) as anzahl
	, sum(case when d.meter = null then 0 else d.meter) as meter
from weilhammer_lager_2 l 
left join weilhammer_datenbank d on 
	d.lager = l.lager and d.regal = l.regal and d.hoehe = l.hoehe and d.fach = l.fach
where lager = '2' and regal = '1' and hoehe = '1' and fach = '1'
group by l.lager, l.regal, l.hoehe, l.fach

selektiert dir genau das was dein script (bzw der teil der gepostet wurde) macht.
Es sagt dir wieviele Datensätze in jedem Regal liegen und wieviele meter das braucht.
Wenn du nur die belegten Fächer benötigst kannst du einen inner join machen und es wird noch einmal schneller.
Die Abfrage sollte (wenn deine Hardware nicht unterdimensioniert ist und Frage 2 mit Ja beantwortet werden kann) in wenigen Sekunden fertig sein.

Führ das ganze zum Testen mal direkt auf der Datenbank aus (bzw ohne Ausgabe) und schau wie lange das geht.

2. Indizes?
Wie schaut dein Datenbankmodell aus? Gibt es einen Index mit (lager, regal, hoehe) in der Tabelle lager_2? Und einen mit (lager, regal, hoehe, fach) in der weilhammer_datenbank?

3. Wiso sind die Felder Lager usw keine numerischen Werte? Das würde auch noch ein bisschen Performance bringen.
 
HI einfach nur crack

Danke für deine Zeit.

Ich habe eine Tabelle weilhammer_lager_2.
In dieser Tabelle gibt es zurzeit 6414 Datensätze.
Jeder Datensatz steht für ein Fach.
Z.B
lager regal hoehe fach
1 1 1 1
1 1 1 2
1 1 1 3
1 1 2 1
1 1 2 2
1 1 2 3
u.s.w


Das Script was ich versuche :) zu schreiben soll:

In die Tabelle weilhammer_lager_2 gehen und soll im Ersten Datensatz
lager,regal,höhe,fach sich merken.
Anschließend mit der Information ind die weihammer_datenbank gehen und alle Datensätze suchen die
in selben lager,regal,höhe,fach liegen.
Die Anzahl der gefundenen Datensätze sollen in der Tabelle weilhammer_lager_2
im Feld anzahl_ordner gespeichert werden.

Gleichzeitig soll in den gefundenen Datensätze in der Tabelle weilhammer_datenbank
die Summe des Feldes meter in das Feld anzahl_meter in die Tabelle weilhammer_lager_2 eingetragen werden.


Das wars schon :).

Ich habe noch keine Erfahrung mit LEFT JOIN, JOIN oder was es sont noch gibt :(

Da die Tabelle weihammer_datenbank ca. 1,5 Millionen Datensätze enthält müßte das Script
6414 Mal in der Tabelle weihammer_datenbank 1,5 Millionen Datensätze durchsuchen.
Das kann dauern....

Die Ausgabe
Es sind 13 Datensätze vorhanden.
Es sind 1.5 Meter vorhanden.
Sollte mir nur Anzeigen ob das Script funktioniert.

Ich hoffe ich habe es Dir einigermaßen verständlich erklären können!

Gruß Thomas


Hier ist ein phpMyAdmin SQL Dump mit den wichtigsten Feldern.

CREATE TABLE IF NOT EXISTS `weilhammer_lager_2` (
`id` int(8) NOT NULL AUTO_INCREMENT,
`lager` int(3) NOT NULL,
`regal` int(3) NOT NULL,
`hoehe` int(3) NOT NULL,
`fach` int(3) NOT NULL,
`blech_tiefe` int(3) NOT NULL,
`fach_art` int(1) NOT NULL,
`belegt` int(1) NOT NULL,
`belegbar` int(1) NOT NULL,
`anzahl_meter` int(5) NOT NULL,
`anzahl_ordner` int(5) NOT NULL,
`beschreibung` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `lager` (`lager`,`regal`,`hoehe`,`fach`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=6414 ;


CREATE TABLE IF NOT EXISTS `weilhammer_datenbank` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`firma` int(4) DEFAULT NULL,
`afid` int(6) DEFAULT NULL,
`lager` int(3) DEFAULT NULL,
`regal` int(3) DEFAULT NULL,
`hoehe` int(3) DEFAULT NULL,
`fach` int(3) DEFAULT NULL,
`meter` double(3,1) DEFAULT NULL,
`bezeichnung` text,
PRIMARY KEY (`id`),
UNIQUE KEY `Firma_AFID` (`firma`,`afid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1417788 ;
 
Zurück