10
Datenbank - Probleme mit Joins
SELECT Abfrage mit Joins und zu grossen Tabellen
Hallo,
ich aktualisiere hier gerade ein paar PHP Script. Darunter komme kommt auch solch eine Abfrage:
jetzt habe ich das problem, dass das join left userminutes_ap so gross ist, dass die abfrage zwischen einer und 4 minuten dauert.
wie kann ich das besser implementieren? habe nur absolutes basiswissen auf dem gebiet. weitere infos stelle ich gerne nach.
gruesse
wolfgang
ich aktualisiere hier gerade ein paar PHP Script. Darunter komme kommt auch solch eine Abfrage:
select freeips.ip, ips.name, aps.mac, aps.wlanmac, ips.user, ips.alive, ips.offline as "failure", areas.area as "ashort", areas.name as "area", buildings.shortcut as "bshort", buildings.name as "building", institutes.institute as "ishort", institutes.name as "institute", buildings.address as "address", aps.place as "location", aps.cover, apmodels.company, apmodels.model, apmodels.standard, aps.lwapp, sum(ua.avg_min*ua.breakpoints)/sum(ua.breakpoints) avg_min, (sum(ua.breakpoints)) breakpoints, (sum(ua.error_bps)) error_bps, sum(ua.errors) errors, sum(ua.errors*ua.avg_resolve_time)/sum(ua.errors) avg_resolve_time from ips left join aps on (ips.ap_id=aps.id) left join apmodels on (aps.apmodel_id=apmodels.id) left join institutes on (institutes.id=aps.institute_id) left join buildings on (institutes.building_id=buildings.id) left join areas on (areas.id=buildings.area_id) left join userminutes_ap ua on (ua.name=ips.name) left join freeips on (ips.freeip_id=freeips.id) where ips.display="Yes" and ips.alive IS NOT NULL and ips.checktype <> "pending" and (ua.date>="2010-07-28" or ua.date is null) group by ips.name wie kann ich das besser implementieren? habe nur absolutes basiswissen auf dem gebiet. weitere infos stelle ich gerne nach.
gruesse
wolfgang
Share on Facebook
Share on Twitter
Share on Reddit
Share on LinkedinPlease also mark the comments that contributed to the solution of the article
Content-Key: 148315
Url: https://administrator.de/contentid/148315
Printed on: April 19, 2024 at 18:04 o'clock
10 Comments
Latest comment
ich nehme mal an, daß man die Abfrage nicht mehr kürzen kann
daher würde ich über alle, in den ON Klauseln erwähnten Tabellen Spalten, einen Index erzeugen.
Je nach Anwendung könnte es sinnvoll sein diese Abfrage von einem Cronjob machen zu lassen, welcher die fertigen Daten in eine temp. Tabelle speichert. Die abfragen in der Anwendungen müssten dann nur diese temp. Tabelle dann abfragen.
from
ips
left join aps on (ips.ap_id=aps.id)
left join apmodels on (aps.apmodel_id=apmodels.id)
left join institutes on (institutes.id=aps.institute_id)
left join buildings on (institutes.building_id=buildings.id)
left join areas on (areas.id=buildings.area_id)
left join userminutes_ap ua on (ua.name=ips.name)
left join freeips on (ips.freeip_id=freeips.id)daher würde ich über alle, in den ON Klauseln erwähnten Tabellen Spalten, einen Index erzeugen.
Je nach Anwendung könnte es sinnvoll sein diese Abfrage von einem Cronjob machen zu lassen, welcher die fertigen Daten in eine temp. Tabelle speichert. Die abfragen in der Anwendungen müssten dann nur diese temp. Tabelle dann abfragen.
mmh.. das mit dem cronjob wäre eine möglichkeit....
einen index haben alle erwähnten spalten...
das problem ist, dass das "copyinh to tmp table" so ewig lang dauert, da die tabelle userminutes_ap so ewig gross ist (8*50000 felder), alle anderen sind mit max 10*1000 bedeutend geringer.
ich überlege, die userminutes mit einem select rauszuholen und die where klausel dann in php umzusetzen. also meine 2 results dort abzugleichen. ob das aber schneller ist, weiß ich nicht. würde mich wundern...
einen index haben alle erwähnten spalten...
das problem ist, dass das "copyinh to tmp table" so ewig lang dauert, da die tabelle userminutes_ap so ewig gross ist (8*50000 felder), alle anderen sind mit max 10*1000 bedeutend geringer.
ich überlege, die userminutes mit einem select rauszuholen und die where klausel dann in php umzusetzen. also meine 2 results dort abzugleichen. ob das aber schneller ist, weiß ich nicht. würde mich wundern...
Stimme nxclass zu und imho werden zuviele Indizies erzeugt, was richtig bremst.
Eine umstrukturierung bzw. normalisierung oder temporäre Tabelle bzw. View könnten da helfen.
Eine umstrukturierung bzw. normalisierung oder temporäre Tabelle bzw. View könnten da helfen.
ich schau mal, sonst melde ich mich nochmal.
Danke
Grüße
Wolfgang
Danke
Grüße
Wolfgang
ips.display="Yes" and
ips.checktype <> "pending" and
ips.checktype <> "pending" and
...und die Spalten sind natürlich enums?
50k Zeilen ist für eine MySQL-DB üblicherweise nicht groß.
Groß fängt ab 5M an.
Normalerweise sind zu lange Selects immer ein Zeichen für falsche Indizierung
(Bsp. Hatte ich einen Query der 15s gebraucht hat und nur durch einen richtigen Index auf 50ms reduziert wurde).
Es kann aber auch einfach an zu wenig Server-Ressourcen liegen.
jep sind beide enums.
was heisst falsche indizierung.
bisher sind nur die id spalten und zusätlich die spalten, die in den join klauseln noch dazu benötigt werden indiziert.
ausnahmen bildet die tabelle userminutes_ap, dort wird nach name_datum indiziert.
was heisst falsche indizierung.
bisher sind nur die id spalten und zusätlich die spalten, die in den join klauseln noch dazu benötigt werden indiziert.
ausnahmen bildet die tabelle userminutes_ap, dort wird nach name_datum indiziert.
Hallo Wolfgang,
die Summierungen beziehen sich ja nur auf die userminutes_ap. Die Filter beziehen sich ausschließlich auf ips und userminutes_ap. Also kann man erst mal diese beiden Tabellen zusammenlesen, filtern und gruppieren und dann das Ganze erst mit den anderen Tabellen aufblähen.
Gruß, Mad Max
die Summierungen beziehen sich ja nur auf die userminutes_ap. Die Filter beziehen sich ausschließlich auf ips und userminutes_ap. Also kann man erst mal diese beiden Tabellen zusammenlesen, filtern und gruppieren und dann das Ganze erst mit den anderen Tabellen aufblähen.
Gruß, Mad Max
d.h. das ich die joins umstellen muss? oder benötige ich dafür 2 sql abfragen. ich weiss nicht, wie sql das intern verarbeitet.
grüße
wolfgang
grüße
wolfgang
Moin herates,
und ergänzend zu Mad Max' Hinweis:
Dazu wäre dann ein zusätzlicher Index auf die Felder "ua_date, ua_name" der "userminutes_ap" sinnvoll.
Dann kannst du diese Spassbremse hier:
vorher auflösen und ZUERST (nur) die Sätze aus der Ua_userminutes holen ">=2010-07-28"
Zwei, drei Sachen verstehe ich ohnehin nicht ganz:
Grüße
Biber
und ergänzend zu Mad Max' Hinweis:
Dazu wäre dann ein zusätzlicher Index auf die Felder "ua_date, ua_name" der "userminutes_ap" sinnvoll.
Dann kannst du diese Spassbremse hier:
.... and (ua.date>="2010-07-28" or ua.date is null) ...
vorher auflösen und ZUERST (nur) die Sätze aus der Ua_userminutes holen ">=2010-07-28"
Zwei, drei Sachen verstehe ich ohnehin nicht ganz:
- Wie reagiert denn deine DB auf die Kombination "ips left join userminutes_ap" (also ua-Werte könnten NULL sein verbunden mit der Sum(ua-Felder)-Klamotte??
- Wiese fängt der ganze Krams denn ausgehend von der "ips"-tabelle an und nicht von "ua"?
- kennt denn mySQL nicht irgendeine EXPLAIN-Funktionalität? ist doch eine echte Datenbank.... und selbst Access kann das seit Version '95
Grüße
Biber
Hallo Wolfgang,
keine Ahnung, ob Deine DB folgende Syntax verkraftet:
Ansonsten könntest Du den Krempel in Klammern in eine Temptabelle schaffen und eben diese Tabelle in Deiner Abfrage einbinden.
Zu Bibers Hinweis, das Datum vorher abzuchecken:
Damit mußt Du achtgeben, daß Du die Abfrage nicht verfälschst, weil Du mit left joins arbeitest. Momentan fliegen DS raus, für die DS in userminutes_ap existieren und alle ua.date < "2010-07-28". Wenn Du das Datum vorher prüfst, bleiben die DS drin, aber die Summen werden null (oder 0?).
Gruß, Mad Max
keine Ahnung, ob Deine DB folgende Syntax verkraftet:
select tmp.name, ...
from (
select ips.name, ...
sum(ua.avg_min*ua.breakpoints)/sum(ua.breakpoints) as avg_min, ...
from ips
left join userminutes_ap ua on (ua.name=ips.name)
where ips.display="Yes" and
ips.alive IS NOT NULL and
ips.checktype <> "pending" and
(ua.date>="2010-07-28" or ua.date is null)
group by ua.name
) tmp
left join aps on (tmp.ap_id=aps.id)
left join apmodels on (aps.apmodel_id=apmodels.id)
left join institutes on (institutes.id=aps.institute_id)
left join buildings on (institutes.building_id=buildings.id)
left join areas on (areas.id=buildings.area_id)
left join freeips on (tmp.freeip_id=freeips.id)Ansonsten könntest Du den Krempel in Klammern in eine Temptabelle schaffen und eben diese Tabelle in Deiner Abfrage einbinden.
Zu Bibers Hinweis, das Datum vorher abzuchecken:
Damit mußt Du achtgeben, daß Du die Abfrage nicht verfälschst, weil Du mit left joins arbeitest. Momentan fliegen DS raus, für die DS in userminutes_ap existieren und alle ua.date < "2010-07-28". Wenn Du das Datum vorher prüfst, bleiben die DS drin, aber die Summen werden null (oder 0?).
Gruß, Mad Max
