✔ MySQL: sessourceschonende Abfrage

Microhome

Microhome

Erfahrenes Mitglied
Hallo zusammen,

ich möchte gern einen Vergleich via MySQL Abfrage anstellen und die Anzahl der Häufigkeiten von Übereinstimmungen, der Reihenfolge nach sortiert, zurückbekommen.

Hierzu ein kleines Beispiel:

Tabelle 1: id | hobby
Tabelle 2: id | name | hobby

Die Tabellen haben folgende Inhalte:

Tabelle 1:

1 | Schwimmen
2 | Tennnis
3 | Klavier
4 | Handball


Tabelle 2:
1 | Robin | 1,3
2 | Lars | 1,2,3
3 | Laura | 2
4 | Marc | 3,4


Nehmen wir an, ich sei Robin, dann möchte ich folgende Reihenfolge durch möglichst eine Abfrage zurückbekommen: 2,4,3


Nun meine Fragen:

a) Wie würdet ihr die Hobbys in Tabelle 2 speichern? Also String, also Zahlen mit Komma getrennt, oder aber bspw. als serialisiertes Array? Oder bietet es sich sogar an eine dritte Tabelle zu erstellen (userid | hobbyid), in welcher jede Zuordnung einen eigenen Eintrag bekommt?

b) Wie müsste die oben gewünschte Abfrage in MySQL möglichst ressourceschonend aussehen? Gehen wir davon aus, dass es 10.000 verschiedene Hobbys gibt und 10 Mio Namen.

c) Gibt es hierfür womöglich eine eigene MySQL Funktion?



Um die Übereinstimmungen zu suchen, würde ich bspw. folgende Abfrage verwenden. Allerdings ist hier das Problem, dass eben Zeile für Zeile durchgegangen werden muss, was bei einer Vielzahl an Einträgen wahrscheinlich jedes Mal viel zu lang dauert. Schön wäre es, wenn MySQL eine Funktion zur Verfügung stellt (Frage c), die direkt nach der Häufigkeit sucht und so z.b. nach den 10 häufigsten Treffern aufhört weiterzusuchen.


Code: 
$pattern = implode('|', $hobbies);
$sql = "
    SELECT *
    FROM tabelle2
    WHERE hobbies REGEXP '{$pattern}";



Vielen Dank für die Hilfe und ich bin sehr auf eure Vorschläge und Meinungen gespannt!
 
Hallo,

ich würde die Tabelle 2 anders aufbauen, ich würde hierfür auch eine zusätzliche Benutzer tabelle machen:
Code: 
mysql> select * from hobby_user;
+----+-------+----------+
| id | name  | hobby_id |
+----+-------+----------+
|  1 | Robin |        1 |
|  2 | Robin |        3 |
|  3 | Lars  |        1 |
|  4 | Lars  |        2 |
|  5 | Lars  |        3 |
|  6 | Laura |        2 |
|  7 | Marc  |        3 |
|  8 | Marc  |        4 |
+----+-------+----------+

Dann bekommst du mit diesem SQL Statement:
SQL: 
select hu2.name, count(*) as gemeinsameHobbies, group_concat(h.hobby) as Hobbies
from hobby_user as hu
join hobby_user hu2 on hu.hobby_id = hu2.hobby_id
join hobbies as h on hu2.hobby_id = h.id
where
  hu2.name <> hu.name and 
  hu.name= 'Robin' 
group by hu2.name 
order by gemeinsameHobbies desc
diese Ausgabe
Code: 
+------+-------------------+-------------------+
| name | gemeinsameHobbies | Hobbies           |
+------+-------------------+-------------------+
| Lars |                 2 | Schwimmen,Klavier |
| Marc |                 1 | Klavier           |
+------+-------------------+-------------------+

Wie man die noch rein bekommt, die keine Gemeinsamkeiten haben bin ich noch am überlegen.

Gruss
Alex
 
Zuletzt bearbeitet:
Ok manchmal gehts einfacher als man anfangs denkt:
Ich habe für jeden noch das Hobby '0' in die hobby_user Tabelle geschrieben:
SQL: 
insert into hobby_user (name,hobby_id) values ('Robin',0),('Lars',0),('Laura',0),('Marc',0);

Danach noch die Abfrage noch ein bisschen angepasst:
SQL: 
select hu2.name, count(*)-1 as gemeinsameHobbies, ifnull(group_concat(h.hobby),"") as Hobbies 
from hobby_user hu 
join hobby_user hu2 on hu.hobby_id = hu2.hobby_id 
left join hobbies as h on hu2.hobby_id = h.id 
where 
  hu2.name <> hu.name and 
  hu.name= 'Robin' 
group by hu2.name 
order by gemeinsameHobbies desc;
und schon erhält man folgendes:
Code: 
+-------+-------------------+-------------------+
| name  | gemeinsameHobbies | Hobbies           |
+-------+-------------------+-------------------+
| Lars  |                 2 | Schwimmen,Klavier |
| Marc  |                 1 | Klavier           |
| Laura |                 0 |                   |
+-------+-------------------+-------------------+

Gruss
Alex
 
Wow Alex, ganz lieben Dank für deine Mühen!

Ich werde das ganze gleich mal testen!

Die Frage ist nun, ob das so auch noch praktikabel ist, wenn Tabelle 2 zb. 10.000 Einträge enthält?! Werde ich am besten gleich mit ausprobieren.

Gibt es eigentlich irgendwie die Möglichkeit in MySQL zu sagen: "Fange bei den besten Treffern an, gibt mir diese zurück, und höre auf zu suchen, wenn du die 10 besten Übereinstimmungen gefunden hast!" ****
 
Zuletzt bearbeitet:
Für das ist eine Datenbank da.
Was du abbilden willst ist eine m:n Beziehung zwischen Personen und Hobbies und das passiert nunmal über eine extra Tabelle.

Falls du nur die besten 10 Ergebnisse haben willst, dann schreibst du dahinter noch ein LIMIT 10.
Wobei du natürlich, wenn du z.b. 12 personen mit 5 Gemeinsamkeiten hast dann einfach 2 nicht dabei hast.

Gruss
Alex
 
Hey Alex,

ja, das mit dem LIMIT ist mir natürlich bewusst. Nur würde die MySQL Abfrage ja trotzdem erstmal ALLE Einträge mit der "Vorlage" überprüfen und wenn da in der DB tausende m:n Einträge vorhanden sind, würde das sicher eine Ewigkeit dauern, oder?


Beste Grüße und schonmal einen guten Start ins Wochenende!
 
Aber anders gehts nicht.
Du willst einen Vergleich aller Benutzer miteinander, daher kannst du erst mit dem letzten Vergleich sicher sein wer unter die Top10 kommt.

Passende Indizes bei den Tabellen vorausgesetzt sollte sich der Aufwand in Grenzen halten. Wenn du mit deinem DB Schema arbeitest, dann musst du immer alle Daten aus der DB Abfragen und dann nochmal auf jede Spalte mit den IDs der Hobbies eine regexp ausführen.

Google mal nach regexp und Performance :)

Wenn du mit Millionen Datensätzen arbeitest, dann wirds sicher dauern, aber was du machen willst geht auch nur bis zu einem gewissen Maß ressourcenschondend.

Gruss
Alex
 
Hallo Alex,

nein, ich werde definitiv nicht meine Tabellenvariante nehmen, sondern deinen Vorschlag übernehmen. Darum hatte ich auch gefragt, ob es eben sinnvoller ist das ganze m:n zu mappen.

Ich werde mal eine bestimme Anzahl zufälliger Einträge generieren und schauen, wie sich die Zugriffszeit verändert.


Ersteinmal ganz herzlichen Dank für deine Hilfe und ein hoffentlich sonniges Wochenende!
 
Ich nochmal... leider bekomme ich folgenden Error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group_concat(h.hobby) AS Hobbies JOIN hobby_user hu2 ON hu.hobby_id = hu2.hobby_' at line 1
Zum Vergrößern anklicken....

über den Query

Code: 
SELECT hu2.name, COUNT(*) AS gemeinsameHobbies FROM hobby_user AS hu, group_concat(h.hobby) AS Hobbies
JOIN hobby_user hu2 ON hu.hobby_id = hu2.hobby_id
JOIN hobbies AS h ON hu2.hobby_id = h.id
WHERE
  hu2.name <> hu.name AND 
  hu.name= 'Robin' 
GROUP BY hu2.name 
ORDER BY gemeinsameHobbies DESC

Meine Tabellen:

hobbies: id | hobby
hobby_user: id | name | hobby_id


Stehe ich da gerade irgendwie auf dem Schlauch?!
 
Zuletzt bearbeitet:
Ahh ich seh den Fehler :)

Du hast das Group_Concat nach dem FROM im Select eingefügt. So kanns nichts werden *g*
Poste SQL Code bitte mit dem sql Tag, dann wirds auch farblich passend markiert.
 
Um antworten zu können musst du eingeloggt sein.

Neue Beiträge

Zurück