MySQL: Ideen zur Query-Optimierung (tmp-Table vermeiden)

[AllesMeins]

Hi,

ich arbeite zur Zeit mit einer großen Tabelle, auf der ich folgende Aufgabe ausführen will/muss:

Die Tabelle enthält gehörte Musiktitel von Usern. Also ein Feld "userName" und eines Namens "titelUrl" (ist etwas missverständlich benannt, ist keine URL im Sinne einer Webadresse, seht es einfach als eindeutige Id an). Nun will ich die Gemeinsamkeiten zwischen jedem Userpaar ermitteln. Also welche User haben mehr als XX Titel gemeinsam gehört. Man sieht also schon, das das kein Query im Sekundenbereich werden wird, aber im Moment liegen die erwarteten Laufzeiten im Bereich von Monaten und das würde ich doch gerne noch etwas reduzieren. Das größte Problem derzeit ist wohl, das ich bisher nicht darum komme das Anlegen von Temporären Tabellen zu vermeiden - und bei zig Millionen Einträgen dauert das natürlich. Mein derzeitige Stand ist:

  
SELECT  
COUNT(b.userName) as c  
FROM userTitel a  
INNER JOIN userTitel b ON b.titelUrl = a.titelUrl  
WHERE a.userName != b.userName  
GROUP BY b.userName  
HAVING c > 20;  

Explain:

+----+-------------+-------+-------+---------------+----------+---------+------------------------------+----------+----------------------------------------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref                          | rows     | Extra                                        |
+----+-------------+-------+-------+---------------+----------+---------+------------------------------+----------+----------------------------------------------+
|  1 | SIMPLE      | a     | index | titelUrl      | userName | 767     | NULL                         | 38401832 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | b     | ref   | titelUrl      | titelUrl | 767     | db.a.titelUrl                |        3 | Using where; Using index                     |
+----+-------------+-------+-------+---------------+----------+---------+------------------------------+----------+----------------------------------------------+

Das ist, wie gesagt, bisher nur ein Ansatz. Ich bin durchaus offen für Vorschläge, die das Konzept über den Haufen werfen, oder Teile der Arbeit in die darunterliegende Anwendung verlagern. Falls ich in der Anwendung rechnen soll, ist es nur wichtig, dass die Daten als Stream verwarbeitet werden können. Die Datenmenge ist zu groß um erst alle Ergebnisdatensätze in den Speicher der Anwendung zu laden und sie danach zu verarbeiten.
Das Ergebnis muss auch nicht 100% korrekt sein, falls euch also spontan Methoden einfallen mit den man das Ergebnis abschätzen kann, wäre dies auch in Ordnung. Es sollte nur einen möglcisht großen Anteil der User finden, die genug Titel gemeinsam haben

[ChrisB]

Hi,

https://forum.selfhtml.org/?t=204404&m=1384216
</hilfe/charta.htm#keine-doppelpostings>

MfG ChrisB

[AllesMeins]

Ich wusste es. Und ich wusste sogar, dass du es sein wirst, der das hier behaupten wird.
Bitte, wenn du hier schon den Erzieher machst, dann mach es bitte richtig. Das ist KEIN Doppelposting. Es geht um die selbe Anwendung, aber es ist ein anderes Problem, eine andere Fragestellung und ein anderer Query.

[Matthias Apsel]

Om nah hoo pez nyeetz, AllesMeins!

Das ist KEIN Doppelposting.

selbst wenn du Recht hast, hätte die Nachfrage an der Ausgangsstelle nicht geschadet - im Gegenteil.

Merke: Im Zweifelsfalle immer im Originalthread bleiben.

Matthias

[AllesMeins]

selbst wenn du Recht hast, hätte die Nachfrage an der Ausgangsstelle nicht geschadet - im Gegenteil.

Das sehe ich anders. Ganz abgesehen davon, das mir im Ausgangsthread offensichtlich keiner helfen konnte, sind es nunmal zwei verschiedene Fragen gewesen - im anderen Thread wollte ich wissen ob sich das Ergebnis eines Subselects irgendwie wiederverwenden lässt - hier habe ich einen komplett anderen Query und frage mich, wie sich das Anlegen von tmp-Tables vermeiden lässt. Denke nicht, dass es der Übersichtlichkeit in irgend einer Form geholfen hätte, wenn ich diese beiden Fragen zusammengeworfen hätte, nur weil sie in meinem Fall "zufällig" auf der selben Tabelle arbeiten sollen.

[Ilja]

moin,

Also ein Feld "userName" und eines Namens "titelUrl"

beides ein wenig unglücklich in meinen augen, ich würde hier drei tabellen erwarten, eine für die user, eine für die titel und eben eine beziehungstabelle. ändert sich in deinem falle zum beispiel der username, müssen sich die schlüssel mit verändern. das ist bei systemen, wo es nur um die auswertung drauf ankommt (OLAP) nicht so relevant. fragt sich, ob dein system nur der auswertung dient, oder doch eher um eine OLTP datenank.

Man sieht also schon, das das kein Query im Sekundenbereich werden wird

50 millionen einträge ist schon eine etwas größere sache, nichts was in der praxis nicht vorkommt. aber wenn es hier um den einsatz in einem im geschäftlichen umfeld geht, dann stellt sich auch die frage geeigneter hardware und software. die frage ist auch, wieviele user in der tabelle enthalten sind. wenn es zum beispiel 100.000 verschiedene user sind, dann würdest du eine ergebnismenge von 100.000 * 99.999 bekommen, das geht in die milliarden.

SELECT
COUNT(b.userName) as c
FROM userTitel a
INNER JOIN userTitel b ON b.titelUrl = a.titelUrl
WHERE a.userName != b.userName
GROUP BY b.userName
HAVING c > 20;

  
du hast da einen logische fehler, du gruppierst nur über eine der user spalten, sprich du bekommst pro user nur einen datensatz zurück geliefert, wo eben die matches der titel aller anderen user aufsummiert werden. wenn ich dich richtig verstanden habe, willst du aber eine auswertung von jedem user zu jeden anderen user. auch der weg über die gruppierung scheint mir ungünstig zu sein. zum weiteren würden datensätze wegfallen, zum beispiel wenn ein user einen sehr aussergewöhnlichen geschmack hat und demzufolge keine gemeinsamkeit in den titel mit anderen users, dann würde der user einfach wegfalllen. hier kommt wieder mein merksatz zum tragen: JOINS sind böse ;-) noch ein anderer hinweis, benutze COUNT(\*), wenn es eh keine NULL werte gibt.  
  
der weg zu einer guten abfrage ist eigentlich immer der gleiche. erst einmal die ergebnismenge sicherzustellen und sich dann gedanken über die projektion zu machen. dafür wäre die aufteilung in drei tabellen günstig, die ich am anfang angesprochen habe. dann würde man einen selfjoin machen, um die gewünschte ergebnismenge bekommen, in der projektion würden dann korrelierte unterabfragen zum einsatz kommen. aber wie gesagt, wenn du diese matrix für alle user mit allen usern machen willst, das geht in die milliarden. fragt sich wofür du solch eine ergebnismenge brauchst.  
  
Ilja

[AllesMeins]

hi,

danke für die Antwort

ändert sich in deinem falle zum beispiel der username, müssen sich die schlüssel mit verändern. das ist bei systemen, wo es nur um die auswertung drauf ankommt (OLAP) nicht so relevant. fragt sich, ob dein system nur der auswertung dient, oder doch eher um eine OLTP datenank.

Darum musst du dir keine Gedanken machen. Es geht um eine reine Auswertung.

die frage ist auch, wieviele user in der tabelle enthalten sind.

Etwa 20.000 - also durchaus auch ne Hausnummer. Allerdings brauche ich nur solche Userpaare, die auch wirklich etwas gemeinsam haben. All die, die nichts gemeinsam haben, können ruhig unter den Tisch fallen.

der weg zu einer guten abfrage ist eigentlich immer der gleiche. erst einmal die ergebnismenge sicherzustellen und sich dann gedanken über die projektion zu machen.

hmm, mein Problem ist ja leider gerade die Ergebnismenge bzw. die größe jener. Ich versuche also Zwischenergebnisse bereits möglichst klein zu halten, damit das ganze Handhabbar bleibt.

Marc

[Ilja]

moin,

Etwa 20.000 - also durchaus auch ne Hausnummer. Allerdings brauche ich nur solche Userpaare, die auch wirklich etwas gemeinsam haben. All die, die nichts gemeinsam haben, können ruhig unter den Tisch fallen.

klar, die wird nicht 20.000 * 19.999 user sein.

hmm, mein Problem ist ja leider gerade die Ergebnismenge bzw. die größe jener. Ich versuche also Zwischenergebnisse bereits möglichst klein zu halten, damit das ganze Handhabbar bleibt.

naja, wie groß die ergebnismenge ist, da habe ich die vermutung, das weißt du ja noch gar nicht. erstens war deine abfrage fachlich falsch, zweitens hatte ich den eindruck, sie wäre noch nicht durchgelaufen. wie gesagt, erst mal die richtige ergebnismenge über die selektion ausarbeiten. das heitß noch nicht, dass du konkrete ergebnisse hast, sondern bezieht sich darauf, wie baue ich die abfrage auf.

das bedeutet, das theoretisch jeder user mit jedem user über 20 titel gemeinsam haben kann, spirch ich brauche eine theoretische ergebnismenge von 20.000 x 19.999. das problem dabei ist, dass du die user alle in der userTitel  tabelle "versteckt" hast, sprich dort haben wir es mit wesentlich mehr datensätzen zu tun. für die auswertung, die du machen willst ist das nicht optimal. wären die user getrennt in einer extra tabelle, das würde meiner vermutung nach eine menge an zeit sparen und wesentlich perfomanter ablaufen.

wie dem auch sei, so musst du über die wesentlich größere tabelle einen selfjoin machen (anstelle nur über die user), wobei du die user schon im vorfeld auschließen kannst, die keine 20 titel haben. davon nimmst du jeweils nur die eindeutigen user, nicht aber die titel. dann hast du zwei bereinigte mengen an usern, die deine mögliche ergebnismenge darstellen. und dann musst du noch festellen, welche user 20 gemeinsamkeiten mit einem anderen user hast. du siehst, das ist deutlich umständlicher, als wenn die user getrennt in einer extra tabelle sind. das sieht in etwa so aus.

SELECT tab1.UserName, tab2.UserName
FROM (SELECT ut1.UserName
      FROM userTitel ut1
      GROUP BY ut1.UserName
      HAVING COUNT(*) >= 20
     ) tab1,
     (SELECT ut1.UserName
      FROM userTitel ut1
      GROUP BY ut1.UserName
      HAVING COUNT(*) >= 20
     ) tab2
WHERE tab1.UserName <> tab2.UserName
AND 20 <= (SELECT COUNT(*)
           FROM userTitel t1, UserTitel t2
           WHERE t1.UserName = tab1.UserName
           AND t2.UserName = tab2.UserName
           AND t1.titelUrl = t2.titelUrl
          )

Ilja