Top-Themen

Aktuelle Themen (A bis Z)

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

Mitglied: herates

herates (Level 1) - Jetzt verbinden

04.08.2010 um 13:07 Uhr, 2831 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 ..
Ähnliche Inhalte
Datenbanken
MySQL - Join
Frage von ThoomaasDatenbanken4 Kommentare

Hallo alle zusammen! Ich habe zwei Tabellen in meiner Datenbank. Die eine beinhaltet Teams und einen Code zum Team ...

Datenbanken
Login Probleme in mysql Datenbank
gelöst Frage von bigbootykillaDatenbanken2 Kommentare

Hi, ich habe hier eine Datenbank auf einer Ubuntu Kiste. Wenn ich mich versuche mit Code: einzuloggen bekomme ich ...

Datenbanken
SQL LEFT JOIN in Spalten
gelöst Frage von StfnCstrDatenbanken5 Kommentare

Hallo, ich sehe mal wieder den Wald vor lauter Bäumen nicht. Ich habe 2 Tabellen Tabelle 1: PK, Vorname, ...

Datenbanken
SQL - JOIN zeigt mehrfaches an
Frage von MiStDatenbanken9 Kommentare

Guten Morgen, ich habe mich frisch mit dem Thema SQL "angefreundet". Jetzt habe ich aber irgendwie ein Problem. Und ...

Neue Wissensbeiträge
Windows 10

USB Maus und Tastatur versagen Dienst unter Windows 10

Erfahrungsbericht von hardykopff vor 1 TagWindows 105 Kommentare

Da steht man ziemlich dumm da, wenn der PC sich wegen fehlender USB Tastatur und Maus nicht bedienen lässt. ...

Administrator.de Feedback
Update der Seite: Alles zentriert
Information von Frank vor 1 TagAdministrator.de Feedback18 Kommentare

Hallo User, die größte Änderung von Release 5.8 ist das Zentrieren der Webseite (auf großen Bildschirmen) und ein "Welcome"-Teaser ...

Humor (lol)

WhatsApp-Nachrichten endlich auch per Bluetooth versendbar

Information von BassFishFox vor 2 TagenHumor (lol)4 Kommentare

Genau darauf habe ich gewartet! ;-) Der beliebte Messaging-Dienst WhatsApp erhält eine praktische neue Funktion: Ab dem nächsten Update ...

Google Android

Googles "Android Enterprise Recommended" für Unternehmen

Information von kgborn vor 2 TagenGoogle Android3 Kommentare

Hier eine Information, die für Administratoren und Verantwortliche in Unternehmen, die für die Beschaffung und das Rollout von Android-Geräten ...

Heiß diskutierte Inhalte
Windows Netzwerk
WSUS4 und Windows 10 Updates automatisch installieren
Frage von sammy65Windows Netzwerk15 Kommentare

Hallo miteinander, ich habe mit einen neuen WSUS Server aufgesetzt Server 2016 darauf einen aktuellen WSUS. Grund, wir stellen ...

Speicherkarten
Vergessliche USB-Sticks?
Frage von hanheikSpeicherkarten14 Kommentare

Ich habe in den letzten Tagen 500 USB-Sticks mit Bilddateien bespielt. Obwohl ich die Dateien mit größter Sorgfalt kopiert ...

Windows Server
NTFS Berechtigungen Ordnerstruktur
Frage von hukahu23489Windows Server11 Kommentare

Hallo, ich bin seit kurzem in einer neuen IT-Abteilung und bin über das Berechtigungskonzept des Unternehmens sehr schockiert. Ich ...

Hyper-V
Hyper-V mit altem XEON-Server. Was ist falsch?
Frage von LollipopHyper-V11 Kommentare

Hallo Bin etwas frustriert. Kleinbetrieb, ca. 15 PC's, 2 Stk. Server mit einigen virtuellen PC's für Fernwartung, VaultServer für ...