CronJob-Service
bei SELFPHP mit ...
|
+ minütlichen Aufrufen
+ eigenem Crontab Eintrag
+ unbegrenzten CronJobs
+ Statistiken
+ Beispielaufrufen
+ Control-Bereich
Führen Sie mit den CronJobs von
SELFPHP zeitgesteuert Programme
auf Ihrem Server
aus. Weitere Infos
|
:: Anbieterverzeichnis ::
Globale Branchen
Informieren Sie sich über ausgewählte Unternehmen im Anbieterverzeichnis von SELFPHP
:: Newsletter ::
Abonnieren Sie hier den kostenlosen
SELFPHP Newsletter!
|
MySQLi/PDO/(MySQL) Anfänger, Fortgeschrittene oder Experten können hier Fragen und Probleme rund um MySQLi/PDO/(MySQL) diskutieren |
29.10.2010, 20:16:09
|
Anfänger
|
|
Registriert seit: Oct 2010
Alter: 51
Beiträge: 19
|
|
AW: COUNT mit JOINs und sub-queries kriechend langsam
Ausgeführt. Leider hat sich nichts geändert:
Code:
SELECT COUNT
(t1.id)
FROM
articles AS t1
LEFT JOIN
labels AS p11
ON
(
p11.artikleid = t1.id
AND
p11.lang = 'deutsch'
AND
p11.deleted = t1.deleted
)
JOIN
suppliers AS p12
ON
(
p12.artikleid = t1.id
AND
p12.deleted = t1.deleted
)
LEFT JOIN
suppliernames as r20
ON
(
r20.id = p12.supplierid
AND
r20.deleted = t1.deleted
)
LEFT JOIN
purchprices AS p13
ON
(
p13.artikleid = t1.id
AND
p13.supplierid = r20.id
AND
p13.deleted = t1.deleted
AND
p13.validfrom = (
SELECT
MAX(p13b.validfrom)
FROM
purchprices AS p13b
WHERE
p13b.artikleid = p13.artikleid
AND
p13b.supplierid = p13.supplierid
AND
p13b.deleted = t1.deleted
AND
p13b.validfrom < '1285521549'
)
)
LEFT JOIN
salesprices AS p15
ON
(
p15.artikleid = t1.id
AND
p15.deleted = t1.deleted
AND
p15.validfrom = (
SELECT
MAX(p15b.validfrom)
FROM
salesprices AS p15b
WHERE
p15b.artikleid = p15.artikleid
AND
p15b.deleted = t1.deleted
AND
p15b.validfrom < '1288203628'
)
)
WHERE
t1.deleted = 0
Code:
EXPLAIN SELECT COUNT(t1.id)...
+----+--------------------+-------+--------+----------------------------------------+-----------------+---------+---------------------------+-------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+--------+----------------------------------------+-----------------+---------+---------------------------+-------+--------------------------+
| 1 | PRIMARY | p12 | index | deleted,idx_art_del_sup | idx_art_del_sup | 9 | NULL | 61271 | Using where; Using index |
| 1 | PRIMARY | t1 | eq_ref | PRIMARY,deleted,idx_id_deleted | PRIMARY | 4 | store_dwh.p12.artikleid | 1 | Using where |
| 1 | PRIMARY | r20 | eq_ref | PRIMARY,deleted | PRIMARY | 4 | store_dwh.p12.supplierid | 1 | |
| 1 | PRIMARY | p13 | ref | deleted,artikleid,supplierid | artikleid | 4 | store_dwh.t1.id | 1 | |
| 1 | PRIMARY | p15 | ref | deleted,artikleid | artikleid | 4 | store_dwh.t1.id | 1 | |
| 1 | PRIMARY | p11 | ref | deleted,artikleid,lang | artikleid | 4 | store_dwh.t1.id | 2 | |
| 3 | DEPENDENT SUBQUERY | p15b | ref | deleted,artikleid,validfrom | artikleid | 4 | store_dwh.p15.artikleid | 1 | Using where |
| 2 | DEPENDENT SUBQUERY | p13b | ref | deleted,artikleid,supplierid,validfrom | artikleid | 4 | store_dwh.p13.artikleid | 1 | Using where |
+----+--------------------+-------+--------+----------------------------------------+-----------------+---------+---------------------------+-------+--------------------------+
8 rows in set (0.00 sec)
Gehe ich recht in der Annahme, dass das Ziel am Ende lautet, von der Zahl 61271 unter rows wegzukommen, bzw. dass die restlichen rows o.k. sind? Muss MySQL nicht so oder so sämtliche Reihen durchlaufen, da die Abfrage so verschachtelt ist?
Grüsse
David
|
29.10.2010, 21:10:34
|
Junior Member
|
|
Registriert seit: Aug 2010
Alter: 14
Beiträge: 395
|
|
AW: COUNT mit JOINs und sub-queries kriechend langsam
Schade, ich dachte durch die Umstellung der Abfrage wird die Gewichtung des Index idx_id_deleted auf articles höher. Egal, kommt vielleicht noch.
MySQL muss natürlich alle betroffenen Datensätze lesen / zählen. Aber Momentan ist der Zugriffpfad einfach noch nicht optimal. Es gibt ja auch noch eine Menge zu tun. Letztlich versuche ich mich sinnvoll ran zu tasten und nach jedem Schritt neu zu testen. Etwas mühselig, aber sehr informativ.
Also geht es weiter zur nächsten Tabelle.
Hier bitte folgende Index löschen und erzeugen. Anschließend bitte wieder den EXPLAIN und die aktuelle CREATE TABLE zeigen.
Code:
ALTER TABLE purchprices
DROP INDEX artikleid,
DROP INDEX deleted,
ADD INDEX idx_art_sup_val_del (artikleid,supplierid,validfrom,deleted);
Grüße
Thomas
__________________
Die SQL-Backstube
Bietet Rezepte, Lösungen und ausführliche Beispiele rund um gesundes SQL und zufriedene Datenbanken.
|
29.10.2010, 21:25:37
|
Junior Member
|
|
Registriert seit: Aug 2010
Alter: 14
Beiträge: 395
|
|
AW: COUNT mit JOINs und sub-queries kriechend langsam
Diese SUB-Select in den JOIN verstehe ich nicht wirklich.
Code:
LEFT JOIN purchprices AS p13
ON ( p13.artikleid = t1.id
AND p13.supplierid = r20.id
AND p13.deleted = t1.deleted
AND p13.validfrom = (SELECT Max(p13b.validfrom)
FROM purchprices AS p13b
WHERE p13b.artikleid = p13.artikleid
AND p13b.supplierid = p13.supplierid
AND p13b.deleted = t1.deleted
AND p13b.validfrom < '1285521549') )
Läßt sich das nicht umschreiben? Bzw. warum muss der MAX(validfrom) abgefragt werden, wenn oben nur die COUNT(t1.id) gezählt werden? Denn Teil habe ich noch nicht verstanden.
Grüße
Thomas
__________________
Die SQL-Backstube
Bietet Rezepte, Lösungen und ausführliche Beispiele rund um gesundes SQL und zufriedene Datenbanken.
|
31.10.2010, 11:53:00
|
Anfänger
|
|
Registriert seit: Oct 2010
Alter: 51
Beiträge: 19
|
|
AW: COUNT mit JOINs und sub-queries kriechend langsam
Ich war so frei, in der Tabelle 'salesprices' auch gleich die Indexe anzupassen:
Code:
ALTER TABLE salesprices
DROP INDEX artikleid,
DROP INDEX deleted,
ADD INDEX idx_art_val_del (artikleid,validfrom,deleted);
Und schon läuft's rund 40% schneller :-) Ich arbeite im Moment auf einem Server der etwas mehr Bums als mein Laptop hat, deswegen kann ich die Zeiten nicht direkt vergleichen. Aber vor den neuen Indizies dauerte die Abfrage 5,2 Sekunden, jetzt 3,2 Sekunden. Das ist doch schonmal was :-)
Allerdings ist die Anzahl 'rows' in der Tabelle 'labels' von 2 auf 18 hochgesprungen. Hat das überhaupt irgendeine nützliche Bedeutung? Ausserdem steht auf der Zeile 'p15' unter 'ref' neu ein 'const'. Ich nehme an, das ist damit zu erklären, dass im Moment alle 'validfrom' und 'deleted' Werte unter p15 dieselben sind.
Ergebnis EXPLAIN:
Code:
+----+--------------------+-------+--------+------------------------------------------+---------------------+---------+--------------------------------------------------+-------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+--------+------------------------------------------+---------------------+---------+--------------------------------------------------+-------+--------------------------+
| 1 | PRIMARY | p12 | index | deleted,idx_art_del_sup | idx_art_del_sup | 9 | NULL | 61270 | Using where; Using index |
| 1 | PRIMARY | t1 | eq_ref | PRIMARY,deleted,idx_id_deleted | PRIMARY | 4 | store_dwh.p12.artikleid | 1 | Using where |
| 1 | PRIMARY | r20 | eq_ref | PRIMARY,deleted | PRIMARY | 4 | store_dwh.p12.supplierid | 1 | |
| 1 | PRIMARY | p13 | ref | supplierid,idx_art_sup_val_del | idx_art_sup_val_del | 8 | store_dwh.t1.id,store_dwh.r20.id | 1 | Using index |
| 1 | PRIMARY | p15 | ref | idx_art_val_del | idx_art_val_del | 5 | const,store_dwh.t1.id | 1 | Using index |
| 1 | PRIMARY | p11 | ref | deleted,artikleid,lang | artikleid | 4 | store_dwh.t1.id | 18 | |
| 3 | DEPENDENT SUBQUERY | p15b | ref | validfrom,idx_art_val_del | idx_art_val_del | 5 | store_dwh.t1.deleted,store_dwh.p15.artikleid | 1 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | p13b | ref | supplierid,validfrom,idx_art_sup_val_del | idx_art_sup_val_del | 8 | store_dwh.p13.artikleid,store_dwh.p13.supplierid | 1 | Using where; Using index |
+----+--------------------+-------+--------+------------------------------------------+---------------------+---------+--------------------------------------------------+-------+--------------------------+
SHOW CREATE TABLE (nur noch der Index Teil - Rest bleibt sich ja stets gleich)
Code:
+---------------+------------------------------------------------------------------------------+
| purchprices | PRIMARY KEY (`id`),
| | KEY `supplierid` (`supplierid`),
| | KEY `validfrom` (`validfrom`)
| | KEY `idx_art_sup_val_del` (`artikleid`,`supplierid`,`validfrom`,`deleted`)
| | ) ENGINE=MyISAM AUTO_INCREMENT=61372 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
+---------------+------------------------------------------------------------------------------+
| salesprices | PRIMARY KEY (`id`),
| | KEY `validfrom` (`validfrom`)
| | KEY `idx_art_val_del` (`deleted`,`artikleid`,`validfrom`)
| | ) ENGINE=MyISAM AUTO_INCREMENT=61371 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
+---------------+------------------------------------------------------------------------------+
Die Subqueries haben den Sinn, abhängig vom aktuellen Datum immer die gültigen Preise anzuzeigen. Da ich aber auch zukünftige Preisänderungen eingeben kann, darf die Abfrage nicht einfach SELECT MAX oder lauten, sonst erhalte ich Preise, die noch nicht gültig sind; < time() geht auch nicht, da mir sonst Preise der vor-vor-vor-letzten Preisänderung mit angezeigt werden.
Beispiel: die letzte Preiserhöhung auf dem Artikel "Holzwurm" war am ('validfrom') 1.1.2010, die vorletzte am 1.1.2009 und die nächste am 1.1.2011. Die Abfrage muss lauten: Zeig mir den Preis der mit heutigem Datum ('time()' = 31.10.2010) gültig ist.
Grüsse
David
|
31.10.2010, 12:26:02
|
Anfänger
|
|
Registriert seit: Oct 2010
Alter: 51
Beiträge: 19
|
|
AW: COUNT mit JOINs und sub-queries kriechend langsam
EDIT: ich verstehe, worauf Du hinauswillst: für den COUNT ist es hier völlig Banane, ob überhaupt ein Preis angezeigt wird. Um diesen Teil nur für den COUNT wegzulassen, käme ich in ein kleines strukturelles Problem: COUNT nutzt exakt denselben MySQL-Query wie SELECT. Wenn dieser nun mit zusätzlichen WHERE Schleifen eingegrenzt wird (ganz unten):
Code:
SELECT COUNT
(t1.id)
FROM
articles AS t1
LEFT JOIN
labels AS p11
ON
(
p11.artikleid = t1.id
AND
p11.lang = 'deutsch'
AND
p11.deleted = t1.deleted
)
JOIN
suppliers AS p12
ON
(
p12.artikleid = t1.id
AND
p12.deleted = t1.deleted
)
LEFT JOIN
suppliernames as r20
ON
(
r20.id = p12.supplierid
AND
r20.deleted = t1.deleted
)
LEFT JOIN
purchprices AS p13
ON
(
p13.artikleid = t1.id
AND
p13.supplierid = r20.id
AND
p13.deleted = t1.deleted
AND
p13.validfrom = (
SELECT
MAX(p13b.validfrom)
FROM
purchprices AS p13b
WHERE
p13b.artikleid = p13.artikleid
AND
p13b.supplierid = p13.supplierid
AND
p13b.deleted = t1.deleted
AND
p13b.validfrom < '1285521549'
)
)
LEFT JOIN
salesprices AS p15
ON
(
p15.artikleid = t1.id
AND
p15.deleted = t1.deleted
AND
p15.validfrom = (
SELECT
MAX(p15b.validfrom)
FROM
salesprices AS p15b
WHERE
p15b.artikleid = p15.artikleid
AND
p15b.deleted = t1.deleted
AND
p15b.validfrom < '1288203628'
)
)
WHERE
t1.deleted = 0
AND
p11.label1
LIKE
'%wurm%'
AND
(
p13.listprice >= '200'
AND
p13.listprice <= '300'
)
basiert das Zählen der Einfachheit halber exakt auf demselben Statement wie die Ausgabe. Darauf ist die ganze Klasse aufgebaut, d.h. wenn ich den COUNT separat aufbauen müsste, muss ich nochmal an die Klasse 'ran. Dann nehme ich lieber in Kauf, dass es für diese Ausgabe schlicht und einfach keine Seitenzahlangabe / Blätterfunktion gibt.
Grüsse
David
|
31.10.2010, 13:21:45
|
Junior Member
|
|
Registriert seit: Aug 2010
Alter: 14
Beiträge: 395
|
|
AW: COUNT mit JOINs und sub-queries kriechend langsam
Hallo David,
Zitat:
Zitat von droehn
basiert das Zählen der Einfachheit halber exakt auf demselben Statement wie die Ausgabe. Darauf ist die ganze Klasse aufgebaut, d.h. wenn ich den COUNT separat aufbauen müsste, muss ich nochmal an die Klasse 'ran. Dann nehme ich lieber in Kauf, dass es für diese Ausgabe schlicht und einfach keine Seitenzahlangabe / Blätterfunktion gibt.
|
"blöde Frage" zwischendurch. Hast Du schon mal
Code:
$result = mysql_query("SELECT * FROM table1", $link);
$num_rows = mysql_num_rows($result);
zum Ermitteln der Treffer probiert? Dann entfällt der Vorab-Query mit dem COUNT() komplett und die Blätterfunktion kann problemlos erzeugt werden.
Unsere bisherigen Tuningmassnahmen helfen aber nicht nur beim COUNT(*), sondern auch bei der "echten" SQL-Abfrage.
Grüße
Thomas
__________________
Die SQL-Backstube
Bietet Rezepte, Lösungen und ausführliche Beispiele rund um gesundes SQL und zufriedene Datenbanken.
|
31.10.2010, 13:38:12
|
Junior Member
|
|
Registriert seit: Aug 2010
Alter: 14
Beiträge: 395
|
|
AW: COUNT mit JOINs und sub-queries kriechend langsam
Zitat:
Zitat von droehn
Ich war so frei, in der Tabelle 'salesprices' auch gleich die Indexe anzupassen:
Code:
ALTER TABLE salesprices
DROP INDEX artikleid,
DROP INDEX deleted,
ADD INDEX idx_art_val_del (artikleid,validfrom,deleted);
Und schon läuft's rund 40% schneller :-) Ich arbeite im Moment auf einem Server der etwas mehr Bums als mein Laptop hat, deswegen kann ich die Zeiten nicht direkt vergleichen. Aber vor den neuen Indizies dauerte die Abfrage 5,2 Sekunden, jetzt 3,2 Sekunden. Das ist doch schonmal was :-)
|
Kein Problem. Ich sehe, Du hast das Prinzip jetzt voll intus.
Wenn ich den EXPLAIN so anschaue, bleibt nur noch eine Möglichkeit, den Index zu optimieren.
Code:
ALTER TABLE labels
DROP INDEX artikleid,
DROP INDEX deleted,
ADD INDEX idx_labels_art_lang_del (artikleid,lang,deleted);
Zitat:
Zitat von droehn
Allerdings ist die Anzahl 'rows' in der Tabelle 'labels' von 2 auf 18 hochgesprungen. Hat das überhaupt irgendeine nützliche Bedeutung? Ausserdem steht auf der Zeile 'p15' unter 'ref' neu ein 'const'. Ich nehme an, das ist damit zu erklären, dass im Moment alle 'validfrom' und 'deleted' Werte unter p15 dieselben sind.
|
Die 18 Rows "schätzt" MySQL für den Zugriff auf die Tabelle "labels". Aber dies ist nicht wesentlich ob 1 oder 18 Rows gelesen werden.
"const" ist der etwas bessere/schneller Zugriff als "ref".
So, jetzt noch bitte einen EXPLAIN ausführen.
Grüße
Thomas
__________________
Die SQL-Backstube
Bietet Rezepte, Lösungen und ausführliche Beispiele rund um gesundes SQL und zufriedene Datenbanken.
|
02.11.2010, 21:22:59
|
Anfänger
|
|
Registriert seit: Oct 2010
Alter: 51
Beiträge: 19
|
|
AW: COUNT mit JOINs und sub-queries kriechend langsam
Zitat:
Zitat von thomas_w
Hast Du schon mal
Code:
$result = mysql_query("SELECT * FROM table1", $link);
$num_rows = mysql_num_rows($result);
zum Ermitteln der Treffer probiert? Dann entfällt der Vorab-Query mit dem COUNT() komplett und die Blätterfunktion kann problemlos erzeugt werden.
|
Funktioniert das auch, wenn der SELECT ein LIMIT 0,30 mitschleppt? Wofür nochmal steht '$link' im mysql_query-Statement?
Ich werde es auf jeden Fall gerne probieren, aber erst wenn Deine erstklassigen Tuningtips aufgebraucht sind ;-)
Ich hatte mit dem letzten Index nochmal eine fabelhafte Performancesteigerung und wir liegen mittlerweile bei knapp 3 Sekunden (statt ehemals fast 10) auf meinem Laptop.
Code:
+----+--------------------+-------+--------+------------------------------------------+-------------------------+---------+--------------------------------------------------+-------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+--------+------------------------------------------+-------------------------+---------+--------------------------------------------------+-------+--------------------------+
| 1 | PRIMARY | p12 | index | deleted,idx_art_del_sup | idx_art_del_sup | 9 | NULL | 61271 | Using where; Using index |
| 1 | PRIMARY | t1 | eq_ref | PRIMARY,deleted,idx_id_deleted | PRIMARY | 4 | store_dwh.p12.artikleid | 1 | Using where |
| 1 | PRIMARY | p11 | ref | lang,idx_labels_art_lang_del | idx_labels_art_lang_del | 43 | store_dwh.t1.id,const,const | 1 | Using index |
| 1 | PRIMARY | r20 | eq_ref | PRIMARY,deleted | PRIMARY | 4 | store_dwh.p12.supplierid | 1 | |
| 1 | PRIMARY | p13 | ref | supplierid,idx_art_sup_val_del | idx_art_sup_val_del | 8 | store_dwh.t1.id,store_dwh.r20.id | 1 | Using index |
| 1 | PRIMARY | p15 | ref | idx_art_val_del | idx_art_val_del | 5 | const,store_dwh.t1.id | 1 | Using index |
| 3 | DEPENDENT SUBQUERY | p15b | ref | validfrom,idx_art_val_del | idx_art_val_del | 5 | store_dwh.t1.deleted,store_dwh.p15.artikleid | 1 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | p13b | ref | supplierid,validfrom,idx_art_sup_val_del | idx_art_sup_val_del | 8 | store_dwh.p13.artikleid,store_dwh.p13.supplierid | 1 | Using where; Using index |
+----+--------------------+-------+--------+------------------------------------------+-------------------------+---------+--------------------------------------------------+-------+--------------------------+
8 rows in set (0.00 sec)
Beim nochmalig Drüberschauen glaube ich allerdings, dass hier Ende vom Lied ist; r20 ist nur noch Namen des Lieferanten aus einer sehr viel kleineren Tabelle auslesen - oder hast Du noch einen in der Trickkiste?
Grüsse
David
|
03.11.2010, 08:30:49
|
Junior Member
|
|
Registriert seit: Aug 2010
Alter: 14
Beiträge: 395
|
|
AW: COUNT mit JOINs und sub-queries kriechend langsam
Hallo David,
Zitat:
Zitat von droehn
Funktioniert das auch, wenn der SELECT ein LIMIT 0,30 mitschleppt? Wofür nochmal steht '$link' im mysql_query-Statement?
Ich werde es auf jeden Fall gerne probieren, aber erst wenn Deine erstklassigen Tuningtips aufgebraucht sind ;-)
|
Das ist ein Beispiel aus dem PHP-Handbuch bzw. aus der PHP-WebSeite. Einfach dort mal nachlesen und ausprobieren. $link ist in diesem Fall die Datenbankverbindung.
Tuning besteht ja aus vielen Komponenten
- Tabellenstruktur
- SQL-Abfrage
- Hardware, RAM, I/O
- Netzwerk
- Datenmenge
- Anzahl User
Meine Möglichkeiten im Rahmen einer freiwilligen kostenlosen Tätigkeit sind jetzt ausgeschöpft. Weiterhin soll eine Datenbank ja nicht nur auf einen Query hin optimiert werden.
Grüße
Thomas
__________________
Die SQL-Backstube
Bietet Rezepte, Lösungen und ausführliche Beispiele rund um gesundes SQL und zufriedene Datenbanken.
|
03.11.2010, 09:04:10
|
Anfänger
|
|
Registriert seit: Oct 2010
Alter: 51
Beiträge: 19
|
|
AW: COUNT mit JOINs und sub-queries kriechend langsam
Moin Thomas,
Zitat:
Zitat von thomas_w
Meine Möglichkeiten im Rahmen einer freiwilligen kostenlosen Tätigkeit sind jetzt ausgeschöpft.
|
Das verstehe ich natürlich und bin Dir umso mehr für Deine wertvollen Tips dankbar! Im Grunde ging es mir am Anfang nur darum, ob die Abfrage ÜBERHAUPT beschleunigt werden kann - ansonsten hätte ich schulterzuckend den Umstand akzeptiert und den COUNT an dieser Stelle weggelassen.
Ich werde ein bisschen am Query rumbasteln und meine Ergebnisse Zwecks Vollständigkeit für andere Forumbesucher hier posten. Ausserdem hält mich derselbe Query als DISTINCT performancemässig auf Trab. Da werde ich mal ein bisschen mit den Indexen rumprobieren.
Auf jeden Fall hat mich Deine Hilfe hier enorm weitergebracht.
Vielen Dank nochmal und schöne Grüsse
David
|
Aktive Benutzer in diesem Thema: 1 (Registrierte Benutzer: 0, Gäste: 1)
|
|
Themen-Optionen |
|
Ansicht |
Linear-Darstellung
|
Forumregeln
|
Es ist Ihnen nicht erlaubt, neue Themen zu verfassen.
Es ist Ihnen nicht erlaubt, auf Beiträge zu antworten.
Es ist Ihnen nicht erlaubt, Anhänge hochzuladen.
Es ist Ihnen nicht erlaubt, Ihre Beiträge zu bearbeiten.
HTML-Code ist aus.
|
|
|
Alle Zeitangaben in WEZ +2. Es ist jetzt 08:59:37 Uhr.
|