Ja, es ist soweit - die ersten "jugendsünden" kommen zu tage... oder besser die einzige.
Nun, wo sich langsam mehr Spieler auf meine Domain verirren, wird das mit der Performance ein Problem... bzw. eigentlich läufts recht gut. Bis auf EINE Sache.
Es handelt sich um ein.. oder genauer gesagt 4 ähnliche Queries, welche die Datenbank stark auslasten, da alle involvierten Tabellen vollständig gelesen werden müssen (nicht gut)... und nun suche ich einen weg, das ganze performant abzuändern, fürchte aber, das ich die selbe Funktionalität bei besserer Performance nur dann erhalte, wenn ich das System dahinter komplett umkremple - wofür ich eigentlich zu Faul bin.
Das besagte Query lautet:
$QUERY = "
SELECT contract_proposals. * , alliances.tag, accounts.nick
FROM contract_proposals
LEFT OUTER JOIN alliances ON ( ( (
partner2 = alliances.id AND partner2_type='A' ) OR ( partner1 = alliances.id
AND partner1_type='A' ) ) AND alliances.id != ".(int)$cpid." )
LEFT OUTER JOIN accounts ON ( ( (
partner2 = accounts.id AND partner2_type='R' ) OR ( partner1 = accounts.id
AND partner1_type='R' ) ) AND accounts.id != ".(int)$uid." )
WHERE (
partner1 = ".(int)$cpid." AND partner1_type = 'A'
) OR (
partner2 = ".(int)$cpid." AND partner2_type = 'A'
)
ORDER BY contract_type";
?>
Daher möchte ich wissen, ob jemand noch Tipps hat, bevor ich anfange, alles umzukrempeln !?
Performance-Problem
gepostet vor 17 Jahre, 5 Monate von AngelFilia
gepostet vor 17 Jahre, 5 Monate von Drezil
ich hoffe mal partnerX_type ist nen enum .. weil char ist da sen performance-killer.
ansonsten indizes auf partnerX und partnerX_type..
das sollte dann alles etwas beschleunigen.
vielleicht auch das * durch die spalten ersetzen .. könnte auch halfen.
außerdem sollte man das ON so knapp wie möglich halten..
was auch noch helfen kann ist 2 querys bauen und die verunionen.. einmal für partner2 und einmal für partner 1 (wenn ich die query im kopf grad richtig interpretiere..)
wie sieht denn grob deine datenstruktur aus? wenn man qrys neu konstruiert, kann man sie einfacher verbessern, als wenn man ein einer bestehenden herumdoktort.
auch ein explaun kann hilfreich sein um herauszufinden, welcher teil der qry nun langsam ist.
ansonsten indizes auf partnerX und partnerX_type..
das sollte dann alles etwas beschleunigen.
vielleicht auch das * durch die spalten ersetzen .. könnte auch halfen.
außerdem sollte man das ON so knapp wie möglich halten..
was auch noch helfen kann ist 2 querys bauen und die verunionen.. einmal für partner2 und einmal für partner 1 (wenn ich die query im kopf grad richtig interpretiere..)
wie sieht denn grob deine datenstruktur aus? wenn man qrys neu konstruiert, kann man sie einfacher verbessern, als wenn man ein einer bestehenden herumdoktort.
auch ein explaun kann hilfreich sein um herauszufinden, welcher teil der qry nun langsam ist.
gepostet vor 17 Jahre, 5 Monate von AngelFilia
partnerX_type ist enum, daran liegts nicht.
Das * bringt nicht viel zu ersetzen, zumal ich dann eh alle spalten aufzählen müsste, das bringts nicht.
Wenn ich das ON kürze kriege ich nicht mehr die passenden Ergebnisse sondern zu viele Werte, die gar nicht dahin gehören.
Problem bei dem ganzen aufbau ist, das man selber als partner1 oder partner2 in der DB stehen kann (damals wollte ich bei einer ohnehin schon kleinen Tabelle platz sparen O.o) und viel sollte das auch nicht bringen... zumal ich von dem Union da auch nix halte, da dann noch einmal mehr alle Tabellen gejoint werden müssen.
Nach dem DB-Log ist das Problem ja, das er die ersten Spalten - das sind noch wenige (so 3) mit denen der zweiten Tabelle (das sind schon um die 50) und dann der dritten (so 400) zusammenpackt und DANN erst raussucht (3x50x400) - das weiß ich von EXPLAIN.
Wenn ich das Query neu bastle werde ich auch die Struktur neubasteln und dann den ganzen lib-Teil - der dann wahrscheinlich stark zusammenschrumpfen wird...
Die Struktur der Tabelle ist folgende:
CREATE TABLE `contracts` (
`id` int(11) unsigned NOT NULL auto_increment,
`partner1_type` enum('R','A') NOT NULL default 'R',
`partner1` int(11) unsigned NOT NULL default '0',
`partner2_type` enum('R','A') NOT NULL default 'R',
`partner2` int(11) unsigned NOT NULL default '0',
`contract_type` enum('W','P','N','A') NOT NULL default 'W',
PRIMARY KEY (`id`),
UNIQUE KEY `contract_partners` (`partner1_type`,`partner1`,`partner2_type`,`partner2`),
KEY `contract_type` (`contract_type`),
KEY `partner1` (`partner1`),
KEY `partner2` (`partner2`)
)
In alliances stehen die Clans (id, name, tag etc.) und in accounts die accounts halt ^^;
Das * bringt nicht viel zu ersetzen, zumal ich dann eh alle spalten aufzählen müsste, das bringts nicht.
Wenn ich das ON kürze kriege ich nicht mehr die passenden Ergebnisse sondern zu viele Werte, die gar nicht dahin gehören.
Problem bei dem ganzen aufbau ist, das man selber als partner1 oder partner2 in der DB stehen kann (damals wollte ich bei einer ohnehin schon kleinen Tabelle platz sparen O.o) und viel sollte das auch nicht bringen... zumal ich von dem Union da auch nix halte, da dann noch einmal mehr alle Tabellen gejoint werden müssen.
Nach dem DB-Log ist das Problem ja, das er die ersten Spalten - das sind noch wenige (so 3) mit denen der zweiten Tabelle (das sind schon um die 50) und dann der dritten (so 400) zusammenpackt und DANN erst raussucht (3x50x400) - das weiß ich von EXPLAIN.
Wenn ich das Query neu bastle werde ich auch die Struktur neubasteln und dann den ganzen lib-Teil - der dann wahrscheinlich stark zusammenschrumpfen wird...
Die Struktur der Tabelle ist folgende:
CREATE TABLE `contracts` (
`id` int(11) unsigned NOT NULL auto_increment,
`partner1_type` enum('R','A') NOT NULL default 'R',
`partner1` int(11) unsigned NOT NULL default '0',
`partner2_type` enum('R','A') NOT NULL default 'R',
`partner2` int(11) unsigned NOT NULL default '0',
`contract_type` enum('W','P','N','A') NOT NULL default 'W',
PRIMARY KEY (`id`),
UNIQUE KEY `contract_partners` (`partner1_type`,`partner1`,`partner2_type`,`partner2`),
KEY `contract_type` (`contract_type`),
KEY `partner1` (`partner1`),
KEY `partner2` (`partner2`)
)
In alliances stehen die Clans (id, name, tag etc.) und in accounts die accounts halt ^^;
gepostet vor 17 Jahre, 5 Monate von Itchy
Was für Monstertabellen mit 400 Spalten hast Du da?
Und das Problem sind glaub ich die LEFT OUTER JOINS - mit denen isses klar, daß die ganzen Tabellen gelesen werden müssen.
Kannst Du evtl. in Worten fassen, was die Query da genau rausziehen soll - das würde zur Optimierung helfen.
Und das Problem sind glaub ich die LEFT OUTER JOINS - mit denen isses klar, daß die ganzen Tabellen gelesen werden müssen.
Kannst Du evtl. in Worten fassen, was die Query da genau rausziehen soll - das würde zur Optimierung helfen.
gepostet vor 17 Jahre, 5 Monate von AngelFilia
Nicht 400 Spalten... 400 ZEILEN (Einträge) und das ist kein Monster...
Ich brauche aus der Tabelle alle Vertragspartner, die entwerder verbünded ('B') einen NAP ('N') im Krieg sind ('W') oder Frieden haben ('P') bzw. bei der Tabelle wollen, davon gibts welche zwischen Allianzen (partnerX_type = 'A'), einzelnen Herrschern (partnerX_type = 'R'), respeketive zwischen Allianzen und Herrschern...
Das LEFT OUTER kann ich dabei nicht weglassen, sonst gibt dieses Query gar nichts zurück, außer man hat zufälligerweise eine Allianz und einen Herrscher mit der selben ID...
desweiteren müssten die Tabellen auch ohne LEFT OUTER gelesen werden, um die passenden Einträge zu finden.
Wende an anderer stelle erfolgreich LEFT OUTER JOIN an ohne merkliche performance-Einbußen... aber das ist auch "nur" zwischen zwei Tabellen.
Zumindest komme ich - je länger ich darüber nachdenke - immer mehr zum Schluss, das ich das neu machen sollte, anstatt Flickwerk zu versuchen.
Ich brauche aus der Tabelle alle Vertragspartner, die entwerder verbünded ('B') einen NAP ('N') im Krieg sind ('W') oder Frieden haben ('P') bzw. bei der Tabelle wollen, davon gibts welche zwischen Allianzen (partnerX_type = 'A'), einzelnen Herrschern (partnerX_type = 'R'), respeketive zwischen Allianzen und Herrschern...
Das LEFT OUTER kann ich dabei nicht weglassen, sonst gibt dieses Query gar nichts zurück, außer man hat zufälligerweise eine Allianz und einen Herrscher mit der selben ID...
desweiteren müssten die Tabellen auch ohne LEFT OUTER gelesen werden, um die passenden Einträge zu finden.
Wende an anderer stelle erfolgreich LEFT OUTER JOIN an ohne merkliche performance-Einbußen... aber das ist auch "nur" zwischen zwei Tabellen.
Zumindest komme ich - je länger ich darüber nachdenke - immer mehr zum Schluss, das ich das neu machen sollte, anstatt Flickwerk zu versuchen.
gepostet vor 17 Jahre, 5 Monate von None
Precaching. Bau dir eine Tabelle auf, wo diese Informationen drin sind.
Dann noch einen Trigger (update/insert/delete) auf die Originaltabelle.
Die Umstellungsarbeiten im Source sind minimal dadurch.
Ist zwar KEINE saubere Lösung, aber du tauscht Plattenplatz gegen Performance ein.
Das gibt dir eventuell ein wenig mehr Zeit für die finale Problemlösung.
Dann noch einen Trigger (update/insert/delete) auf die Originaltabelle.
Die Umstellungsarbeiten im Source sind minimal dadurch.
Ist zwar KEINE saubere Lösung, aber du tauscht Plattenplatz gegen Performance ein.
Das gibt dir eventuell ein wenig mehr Zeit für die finale Problemlösung.
gepostet vor 17 Jahre, 5 Monate von Itchy
Du hast doch geschrieben, 400 Spalten, schrei doch nicht so.
Aber zum eigentlich Problem, das ich jetzt verstehe (anhand von Type wird die Tabelle ausgewählt, die referenziert wird).
Es sollte doch schneller gehn, wenn Du die von Drezil vorgeschlagenen Unions benutzt!
Nach dem DB-Log ist das Problem ja, das er die ersten Spalten - das sind noch wenige (so 3) mit denen der zweiten Tabelle (das sind schon um die 50) und dann der dritten (so 400) zusammenpackt und DANN erst raussucht (3x50x400) - das weiß ich von EXPLAIN.
Aber zum eigentlich Problem, das ich jetzt verstehe (anhand von Type wird die Tabelle ausgewählt, die referenziert wird).
Es sollte doch schneller gehn, wenn Du die von Drezil vorgeschlagenen Unions benutzt!
gepostet vor 17 Jahre, 5 Monate von HSINC
poste mal das explain und die mysql version
gepostet vor 17 Jahre, 5 Monate von AngelFilia
@Itchy:
Sorry, Sorry, wollte nicht schreien, hab da was in der Hitze des Gefechts... (kämpfe gerade auch mit Windows) ... falsch beschrieben und nicht bemerkt.
Ich mach bei Gelegenheit mal einen Vergleich zwischen dem aktuellen und dem jetzigen.
@MrMarco:
Ich nehme lieber direkt ne saubere Lösung und mache alles neu - dürfte vielleicht schneller gehen, als ich denke... sofern ich mir über die neue Struktur im Klaren bin.
@HSINC:
MySQL ist 5.0.22 ...
EXPLAIN liefert folgendes:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE contracts index_merge contract_partners,partner1,partner2 contract_partners,partner2 5,4 NULL 3 Using sort_union(contract_partners,partner2); Using where; Using temporary; Using filesort
1 SIMPLE alliances ALL PRIMARY NULL NULL NULL 37
1 SIMPLE accounts ALL PRIMARY NULL NULL NULL 410
Sorry, Sorry, wollte nicht schreien, hab da was in der Hitze des Gefechts... (kämpfe gerade auch mit Windows) ... falsch beschrieben und nicht bemerkt.
Ich mach bei Gelegenheit mal einen Vergleich zwischen dem aktuellen und dem jetzigen.
@MrMarco:
Ich nehme lieber direkt ne saubere Lösung und mache alles neu - dürfte vielleicht schneller gehen, als ich denke... sofern ich mir über die neue Struktur im Klaren bin.
@HSINC:
MySQL ist 5.0.22 ...
EXPLAIN liefert folgendes:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE contracts index_merge contract_partners,partner1,partner2 contract_partners,partner2 5,4 NULL 3 Using sort_union(contract_partners,partner2); Using where; Using temporary; Using filesort
1 SIMPLE alliances ALL PRIMARY NULL NULL NULL 37
1 SIMPLE accounts ALL PRIMARY NULL NULL NULL 410
gepostet vor 17 Jahre, 5 Monate von Drezil
ich hab mal nen bissl geschaut ..
redesign wäre besser .. oder eine tabelle mit passenden werten (per trigger und so ..)
aber ich hab mal versucht ne neue qry zu bauen, die dasselbe macht. vielleicht ist die ja schneller:
FROM contract_proposals cp
LEFT OUTER JOIN alliances al ON (if(cp.partner2_type = 'A',cp.partner2 = al.id,false) AND al.id != $cpid)
LEFT OUTER JOIN accounts ac ON (if(cp.partner2_type = 'R',cp.partner2 = ac.id,false) AND ac.id != $uid)
WHERE
cp.partner1=$cpid AND cp.partner1_type = 'A')
UNION
(SELECT cp. * , al.tag, ac.nick
FROM contract_proposals cp
LEFT OUTER JOIN alliances al ON (if(cp.partner1_type = 'A',cp.partner1 = al.id,false) AND al.id != $cpid)
LEFT OUTER JOIN accounts ac ON (if(cp.partner1_type = 'R',cp.partner1 = ac.id,false) AND ac.id != $uid)
WHERE
cp.partner2=$cpid AND cp.partner2_type = 'A')
hth
ps: die left outer-joins sind nicht schlimm.. ich mache ständig nen left-outer join auf meine planeten-tabelle .. die hat > 3k einträge .. wenn das on-ding nichts/wenig matcht, dann bleibt das ergebnis auch klein..
redesign wäre besser .. oder eine tabelle mit passenden werten (per trigger und so ..)
aber ich hab mal versucht ne neue qry zu bauen, die dasselbe macht. vielleicht ist die ja schneller:
(SELECT cp. * , al.tag, ac.nick
FROM contract_proposals cp
LEFT OUTER JOIN alliances al ON (if(cp.partner2_type = 'A',cp.partner2 = al.id,false) AND al.id != $cpid)
LEFT OUTER JOIN accounts ac ON (if(cp.partner2_type = 'R',cp.partner2 = ac.id,false) AND ac.id != $uid)
WHERE
cp.partner1=$cpid AND cp.partner1_type = 'A')
UNION
(SELECT cp. * , al.tag, ac.nick
FROM contract_proposals cp
LEFT OUTER JOIN alliances al ON (if(cp.partner1_type = 'A',cp.partner1 = al.id,false) AND al.id != $cpid)
LEFT OUTER JOIN accounts ac ON (if(cp.partner1_type = 'R',cp.partner1 = ac.id,false) AND ac.id != $uid)
WHERE
cp.partner2=$cpid AND cp.partner2_type = 'A')
hth
ps: die left outer-joins sind nicht schlimm.. ich mache ständig nen left-outer join auf meine planeten-tabelle .. die hat > 3k einträge .. wenn das on-ding nichts/wenig matcht, dann bleibt das ergebnis auch klein..
gepostet vor 17 Jahre, 5 Monate von Nuky
hatte vor 2 stunden auch was geschrieben, was nun dem timeout zu opfer gefallen ist.
habs vergessen abzusenden - multibrowser halt.
drezils version sieht aber auch gut aus *gg*
habs vergessen abzusenden - multibrowser halt.
drezils version sieht aber auch gut aus *gg*
gepostet vor 17 Jahre, 5 Monate von HSINC
ich vermute mal das problem ist einfach, das pro query rund 40k results kommen. der rest (*, inhalt der ON,etc) ist einfach vernachlässigbar. das explain schaut auch gut aus, bei ner 4.x hätte ich es auf das OR im where geschoben, die 5 bekommt das gebacken.
eventuell reicht da einfach nen distinct
oder das in 2/3 querys aufsplitten
select ..from contract_proposals
select .. from accounts where id IN (..)
select .. from alliances where id IN (..)
und das ganze im php sinnvoll zusammenbasteln
eventuell reicht da einfach nen distinct
oder das in 2/3 querys aufsplitten
select ..from contract_proposals
select .. from accounts where id IN (..)
select .. from alliances where id IN (..)
und das ganze im php sinnvoll zusammenbasteln
gepostet vor 17 Jahre, 5 Monate von AngelFilia
@HSINC:
Die Werte sind alle distinct - ohne doppelten Eintrag... außerden glaube ich wird dieses Wort erst zum Ende ausgewertet...
@Drezil:
Das sieht sehr gut aus... wusste gar nicht, bzw. kam nicht mal auf die Idee ein IF-Statement in der ON-Klausel zu verwenden. Jedenfalls ist die Temporäre Tabelle und das Filesort weggefallen und DAS hilft schon mal ne Menge...
Damit sollte ich bis zum Redesign über die Runden kommen.
Ein dickes DANKE ^^
@Nuky:
Schade, aber danke für die Mühe ^^
Die Werte sind alle distinct - ohne doppelten Eintrag... außerden glaube ich wird dieses Wort erst zum Ende ausgewertet...
@Drezil:
Das sieht sehr gut aus... wusste gar nicht, bzw. kam nicht mal auf die Idee ein IF-Statement in der ON-Klausel zu verwenden. Jedenfalls ist die Temporäre Tabelle und das Filesort weggefallen und DAS hilft schon mal ne Menge...
Damit sollte ich bis zum Redesign über die Runden kommen.
Ein dickes DANKE ^^
@Nuky:
Schade, aber danke für die Mühe ^^
gepostet vor 17 Jahre, 5 Monate von Drezil
das mit dem if im ON ist mir auch spontan eingefallen .. weil wonach sucht das ON? nur nach einem true/false. und da bei dir bei bla != 'A' bzw 'R' eh false rauskommt dacht ich mir das kann man mit nem if prüfen .. so fallen erstmal einige sachen weg und der rest wird dann im if weitergeprüft.
und das trennen in 2 macht sinn, weil du sonst immer diese (bla or blup) and (foo or bar) hast .. spart nochmal 2-3 vergleiche pro zeile .. ist zwar marginal, aber immerhin etwas ..
und das trennen in 2 macht sinn, weil du sonst immer diese (bla or blup) and (foo or bar) hast .. spart nochmal 2-3 vergleiche pro zeile .. ist zwar marginal, aber immerhin etwas ..
gepostet vor 17 Jahre, 5 Monate von AngelFilia
Dann muss ich mir jetzt nur noch gedanken darüber machen, wie ich das am besten neu strukturiere... wenn es weiter nichts ist ^^;