Top-Themen

AppleEntwicklungHardwareInternetLinuxMicrosoftMultimediaNetzwerkeOff TopicSicherheitSonstige SystemeVirtualisierungWeiterbildungZusammenarbeit

Aktuelle Themen

Administrator.de FeedbackApache ServerAppleAssemblerAudioAusbildungAuslandBackupBasicBatch & ShellBenchmarksBibliotheken & ToolkitsBlogsCloud-DiensteClusterCMSCPU, RAM, MainboardsCSSC und C++DatenbankenDatenschutzDebianDigitiales FernsehenDNSDrucker und ScannerDSL, VDSLE-BooksE-BusinessE-MailEntwicklungErkennung und -AbwehrExchange ServerFestplatten, SSD, RaidFirewallFlatratesGoogle AndroidGrafikGrafikkarten & MonitoreGroupwareHardwareHosting & HousingHTMLHumor (lol)Hyper-VIconsIDE & EditorenInformationsdiensteInstallationInstant MessagingInternetInternet DomäneniOSISDN & AnaloganschlüsseiTunesJavaJavaScriptKiXtartKVMLAN, WAN, WirelessLinuxLinux DesktopLinux NetzwerkLinux ToolsLinux UserverwaltungLizenzierungMac OS XMicrosoftMicrosoft OfficeMikroTik RouterOSMonitoringMultimediaMultimedia & ZubehörNetzwerkeNetzwerkgrundlagenNetzwerkmanagementNetzwerkprotokolleNotebook & ZubehörNovell NetwareOff TopicOpenOffice, LibreOfficeOutlook & MailPapierkorbPascal und DelphiPeripheriegerätePerlPHPPythonRechtliche FragenRedHat, CentOS, FedoraRouter & RoutingSambaSAN, NAS, DASSchriftartenSchulung & TrainingSEOServerServer-HardwareSicherheitSicherheits-ToolsSicherheitsgrundlagenSolarisSonstige SystemeSoziale NetzwerkeSpeicherkartenStudentenjobs & PraktikumSuche ProjektpartnerSuseSwitche und HubsTipps & TricksTK-Netze & GeräteUbuntuUMTS, EDGE & GPRSUtilitiesVB for ApplicationsVerschlüsselung & ZertifikateVideo & StreamingViren und TrojanerVirtualisierungVisual StudioVmwareVoice over IPWebbrowserWebentwicklungWeiterbildungWindows 7Windows 8Windows 10Windows InstallationWindows MobileWindows NetzwerkWindows ServerWindows SystemdateienWindows ToolsWindows UpdateWindows UserverwaltungWindows VistaWindows XPXenserverXMLZusammenarbeit

Datenbank - Probleme mit Joins

Frage Entwicklung Datenbanken

Mitglied: herates

herates (Level 1) - Jetzt verbinden

04.08.2010 um 13:07 Uhr, 2792 Aufrufe, 10 Kommentare

SELECT Abfrage mit Joins und zu grossen Tabellen

Hallo,

ich aktualisiere hier gerade ein paar PHP Script. Darunter komme kommt auch solch eine Abfrage:
01.
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 
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
Mitglied: nxclass
04.08.2010 um 14:43 Uhr
ich nehme mal an, daß man die Abfrage nicht mehr kürzen kann

01.
from 
02.
    ips 
03.
        left join aps on (ips.ap_id=aps.id) 
04.
        left join apmodels on (aps.apmodel_id=apmodels.id) 
05.
        left join institutes on (institutes.id=aps.institute_id) 
06.
        left join buildings on (institutes.building_id=buildings.id) 
07.
        left join areas on (areas.id=buildings.area_id) 
08.
        left join userminutes_ap ua on (ua.name=ips.name) 
09.
        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.
Bitte warten ..
Mitglied: herates
04.08.2010 um 15:11 Uhr
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...
Bitte warten ..
Mitglied: Privateer3000
04.08.2010 um 15:14 Uhr
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.
Bitte warten ..
Mitglied: herates
04.08.2010 um 15:28 Uhr
ich schau mal, sonst melde ich mich nochmal.

Danke

Grüße
Wolfgang
Bitte warten ..
Mitglied: dog
05.08.2010 um 00:13 Uhr
ips.display="Yes" 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.
Bitte warten ..
Mitglied: herates
05.08.2010 um 11:02 Uhr
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.
Bitte warten ..
Mitglied: MadMax
05.08.2010 um 14:46 Uhr
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
Bitte warten ..
Mitglied: herates
05.08.2010 um 14:51 Uhr
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
Bitte warten ..
Mitglied: Biber
05.08.2010 um 14:57 Uhr
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:
.... 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
Bitte warten ..
Mitglied: MadMax
06.08.2010 um 11:05 Uhr
Hallo Wolfgang,

keine Ahnung, ob Deine DB folgende Syntax verkraftet:
01.
select	tmp.name, ... 
02.
from	( 
03.
	select	ips.name, ... 
04.
		sum(ua.avg_min*ua.breakpoints)/sum(ua.breakpoints) as avg_min, ... 
05.
	from	ips 
06.
		left join userminutes_ap ua on (ua.name=ips.name) 
07.
	where	ips.display="Yes" and 
08.
		ips.alive IS NOT NULL and 
09.
		ips.checktype <> "pending" and 
10.
		(ua.date>="2010-07-28" or ua.date is null) 
11.
	group by ua.name 
12.
	) tmp 
13.
	left join aps on (tmp.ap_id=aps.id) 
14.
	left join apmodels on (aps.apmodel_id=apmodels.id) 
15.
	left join institutes on (institutes.id=aps.institute_id) 
16.
	left join buildings on (institutes.building_id=buildings.id) 
17.
	left join areas on (areas.id=buildings.area_id) 
18.
	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
Bitte warten ..
Neuester Wissensbeitrag
Microsoft

Lizenzwiederverkauf und seine Tücken

(5)

Erfahrungsbericht von DerWoWusste zum Thema Microsoft ...

Ähnliche Inhalte
Batch & Shell
gelöst Crontab mit Shell Probleme (9)

Frage von mschaedler1982 zum Thema Batch & Shell ...

Windows 7
gelöst Sind euch verstärkte Probleme bei Windows 7 Updates aufgefallen? (4)

Frage von RadioHam zum Thema Windows 7 ...

Cloud-Dienste
gelöst OwnCloud 8 Probleme mit Vorschaubildern auf Mobilgeräten (1)

Frage von zeroblue2005 zum Thema Cloud-Dienste ...

Heiß diskutierte Inhalte
Windows Netzwerk
Windows 10 RDP geht nicht (16)

Frage von Fiasko zum Thema Windows Netzwerk ...

Windows Server
Outlook Verbindungsversuch mit Exchange (15)

Frage von xbast1x zum Thema Windows Server ...

Microsoft Office
Keine Updates für Office 2016 (13)

Frage von Motte990 zum Thema Microsoft Office ...