✔ Abfragen Optimierung

Sorry fürs Einmischen, aber bei diesen geringen Datenmengen kann das Verhalten nur auf schlechte Indexe zurück zu führen sein oder aber der DB-Server ist von 1990 und hat einfach zu wenig Saft.

Es bringt natürlich Nullkommanichts, wenn für jede Spalte einer tabelle blind und willkürlich ein einspaltiger Index angelegt wird. Es bringt hingegen sehr wohl etwas, zusammen gesetzte Indexe zu verwenden, wenn die Abfrage dies erfordert. Grund hierfür - MySQL verwendet immer nur einen Index einer Tabelle (zu 99%), es nützen also 25 Indexe nichts, wenn sie alle im Bezug auf die Abfrage suboptimal sind.

Sehr hilfreich für weitere Analysen sind für die Community hier folgende Ausgaben:
1. Show Create Table export;
2. Show Create Table mail;
3. paar Angaben zum Server (grob ob das System aktuellen Anforderungen entspricht)

Anschließend kann dir ganz gut geholfen werden. Du wirst dabei jedoch um Analysen mittels EXPLAIN nicht umher kommen.

Grüße BN
 
@Yaslaw: Dann klappt es wunderbar

@bn:
Aber wie ich die Indizes optimal setze weiß ich leider nicht, lese aber nebenbei eine Anleitung zu dem Thema.

1) Create export:
SQL: 
CREATE TABLE IF NOT EXISTS `export` (
  `ID` int(11) NOT NULL auto_increment,
  `packetno` text collate latin1_german1_ci NOT NULL,
  `costumer` text collate latin1_german1_ci NOT NULL,
  `firm` text collate latin1_german1_ci NOT NULL,
  `cost_unit` text collate latin1_german1_ci NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci AUTO_INCREMENT=86430 ;
2) Create mail:
SQL: 
CREATE TABLE IF NOT EXISTS `mail` (
  `ID` int(11) NOT NULL auto_increment,
  `packetno` text collate latin1_german1_ci NOT NULL,
  `amount` decimal(10,3) NOT NULL,
  `tax` tinyint(11) NOT NULL,
  `productno` text collate latin1_german1_ci NOT NULL,
  `scandate` text collate latin1_german1_ci NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci AUTO_INCREMENT=22724 ;

3) Server:
2.6x Linux-Debian 32bit
CPU: 1x 2,6 Ghz
RAM: 1024 MB

Habe den Server eben einmal eine CPU mehr zugewiesen, doch diese wird nicht genutzt.
Hier im Forum habe ich dazu folgende Erklärung gefunden:
Es ist richtig, dass jede Connection zu MySQL Server als ein Thread läuft und daher nicht mehrere CPUs nutzen kann. Abfragen über diese Connection laufen in diesem Thread. Daher sollte immer versucht werden, mit Connection-Pools, also mit mehreren Connections auf MySQL Server zu arbeiten. Nur so können alle Kerne optimal genutzt werden.
Zum Vergrößern anklicken....

Bringt mir bei einer Abfrage also leider gar nichts :(
 
Zuletzt bearbeitet von einem Moderator:
CPU ist nicht alles, wichtiger sind RAM und Festplatten-Speed. 1 GB RAM ist nicht gerade ein Schlaraffenland für den DB Server.

Das Design der beiden Tabellen ist unglücklich.

Vermutung:
packetno - alpahnumerische Zeichenkette mit beschränkter Länge (maximale Länge? => VARCHAR(32)?)
productno - alpahnumerische Zeichenkette mit beschränkter Länge (maximale Länge? => VARCHAR(32)?)
scandate - Datum und Zeit (Text ist hier absoluter Quarck besser TIMESTAMP)
cost_unit - alpahnumerische Zeichenkette mit beschränkter Länge (maximale Länge? => VARCHAR(32)?)

Sollte ich mit der Vermutung alphanumerisch falsch liegen und es sind sogar reine Integer, dann ist der Datentyp Integer immer einem Varchar vorzuziehen.

Diese Veränderung halte ich für unbedingt notwendig. Im Anschluss kann man sich Gedanken über gescheite Indexe machen.

Grüße
 
Festplattengeschwindigkeit ist kein Problem. Maximale Auslastung liegt bei 15.000 kbit/s und das nur beim Backup, durchschnittlich liegt der Wert bei 1000 kbit/s.
Wenn ich mir den belegten RAM ansehe, liegt dieser bei 226 MB und 61MB Overhead-Verbrauch.
Nur die CPU geht bis 99%, sprich 2660 MHz.

packetno und productno sind nur Zahlen, können aber auch mit 0 beginnen, welche nicht verloren gehen darf. Sind auf eine Länge von 15 Zeichen maximiert.
Scandate ist nicht so wichtig, unter Umständen könnte ich es auch raus nehmen!

cost_unit könnte man in einen Int umwandeln, da ich noch keine mit 0 beginnenden Zahlenketten gesehen habe.
 
Nein, sind genau 13 Zeichen, von denen einigen mit 0 beginnen, es wird nicht mit Nullen aufgefüllt.

Die beiden ersten Abfragen laufen mittlerweile wunderbar, in 5 Minuten durch.
Nur die letzte Abfrage, brauch ewig, ich hab eben einmal überschlagen, es würde über 22 Minuten dauern. Werde jetzt einmal testen, ob MySQL es wirklich schafft oder immer abbricht, ab einem Punkt.
SQL: 
SELECT SUM(amount) AS summe 
                                FROM mail 
                                WHERE packetno NOT IN 
                                    (SELECT packetno 
                                    FROM export)

Durch die 2. CPU kann ich aber einen Thread zu 100% ausnutzen, ohne das der Webserver langsamer wird für andere User.
 
Zuletzt bearbeitet von einem Moderator:
Vorweg - mach ein Backup der DB, bevor du Änderungen daran vornimmst. Falls geschehen oder idealerweise die Änderungen zunächst auf einem Testsystem durchgeführt werden - go :)

Bzgl. des Servers - nicht verrückt machen ;-).

Dann stelle den Datentypen von packetno und productno auf VARCHAR(15) um.
SQL: 
ALTER TABLE mail CHANGE paketno paketno VARCHAR(15) collate latin1_german1_ci NOT NULL;
ALTER TABLE mail CHANGE productno productno VARCHAR(15) collate latin1_german1_ci NOT NULL;
ALTER TABLE export CHANGE paketno paketno VARCHAR(15) collate latin1_german1_ci NOT NULL;

Welche Werte stehen in Scandate drin (Beispiel)?

cost_unt nach INT wandeln ist optimal.
SQL: 
ALTER TABLE export CHANGE cost_unit cost_unit INT UNSIGNED DEFAULT 0

Im Anschluss führe bitte die 3 SQL-Kommandos mit einem voran gestellten EXPLAIN aus und poste die Ergebnisse hier. Beispiel auf der SQL-Konsole:
SQL: 
EXPLAIN SELECT feld FROM tabelle WHERE 1=1\G

Grüße BN
 
Zuletzt bearbeitet von einem Moderator:
Nachdem ich alles umgesetzt habe erhalte ich folgendes Ergebnis.

1. Abfrage
SQL: 
-- INSERT INTO mv_tax_sum (tax, cost_unit, summe)
EXPLAIN SELECT tax, cost_unit, SUM( amount ) AS summe
FROM mail AS m
INNER JOIN export AS e ON e.packetno = m.packetno
GROUP BY cost_unit, tax;


Ausgabe:
Code: 
id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra 
1	SIMPLE	m	ALL	IDX_m_packetno	NULL	NULL	NULL	28723	Using temporary; Using filesort
1	SIMPLE	e	ref	IDX_e_packetno	IDX_e_packetno	17	spedition.m.packetno	1

Code: 
object(stdClass)#1 (10) {
  ["id"]=>
  string(1) "1"
  ["select_type"]=>
  string(6) "SIMPLE"
  ["table"]=>
  string(1) "m"
  ["type"]=>
  string(3) "ALL"
  ["possible_keys"]=>
  string(14) "IDX_m_packetno"
  ["key"]=>
  NULL
  ["key_len"]=>
  NULL
  ["ref"]=>
  NULL
  ["rows"]=>
  string(5) "28723"
  ["Extra"]=>
  string(31) "Using temporary; Using filesort"
}
object(stdClass)#2 (10) {
  ["id"]=>
  string(1) "1"
  ["select_type"]=>
  string(6) "SIMPLE"
  ["table"]=>
  string(1) "e"
  ["type"]=>
  string(3) "ref"
  ["possible_keys"]=>
  string(14) "IDX_e_packetno"
  ["key"]=>
  string(14) "IDX_e_packetno"
  ["key_len"]=>
  string(2) "17"
  ["ref"]=>
  string(20) "spedition.m.packetno"
  ["rows"]=>
  string(1) "1"
  ["Extra"]=>
  string(0) ""
}

2. Abfrage:

PHP: 
mysql_query("CREATE INDEX IDX_e_packetno ON export (packetno)");
mysql_query("CREATE INDEX IDX_m_packetno ON mail (packetno)");

SQL: 
EXPLAIN SELECT SUM( amount ) AS summe
FROM mail
WHERE packetno NOT 
IN (

SELECT packetno
FROM export
)

Ausgabe:
Code: 
id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra 
1	PRIMARY	mail	ALL	NULL	NULL	NULL	NULL	28723	Using where
2	DEPENDENT SUBQUERY	export	index_subquery	IDX_e_packetno	IDX_e_packetno	17	func	1	Using index

Code: 
object(stdClass)#1 (10) {
  ["id"]=>
  string(1) "1"
  ["select_type"]=>
  string(7) "PRIMARY"
  ["table"]=>
  string(4) "mail"
  ["type"]=>
  string(3) "ALL"
  ["possible_keys"]=>
  NULL
  ["key"]=>
  NULL
  ["key_len"]=>
  NULL
  ["ref"]=>
  NULL
  ["rows"]=>
  string(5) "28723"
  ["Extra"]=>
  string(11) "Using where"
}
object(stdClass)#2 (10) {
  ["id"]=>
  string(1) "2"
  ["select_type"]=>
  string(18) "DEPENDENT SUBQUERY"
  ["table"]=>
  string(6) "export"
  ["type"]=>
  string(14) "index_subquery"
  ["possible_keys"]=>
  string(14) "IDX_e_packetno"
  ["key"]=>
  string(14) "IDX_e_packetno"
  ["key_len"]=>
  string(2) "17"
  ["ref"]=>
  string(4) "func"
  ["rows"]=>
  string(1) "1"
  ["Extra"]=>
  string(11) "Using index"
}
 
Zuletzt bearbeitet von einem Moderator:
1. Query dauerte ca. 5 Minuten.
2. Query habe ich nach ca. 25 Minuten abgebrochen.

Paradoxerweise wurden eben alle Berechnungen in 240 ms ausgeführt, ich habe danach alle relevanten Daten zum Test aus der DB entfernt und einen Import+Berechnung durchgeführt, es dauert keine 5 Sekunden, danach war alles erledigt. Dies verstehe ich nicht...
 
Um antworten zu können musst du eingeloggt sein.

Neue Beiträge

Zurück