Reine FK-Tabelle - Performant?

Spider · 14.03.2010, 18:11

Hallo,

ich habe eine Tabelle in der 11 Werte gespeichert werden.
Ein Wert ist die Benutzer-ID, die anderen 10 sind IDs auf eine Tabelle "fahrer", in der Fahrernamen gespeichert sind.
Alle diese 11 Spalten sind Foreignkeys, einmal auf benutzer.benutzer_id und 10 mal auf fahrer.fahrer_id

Das ganze sind Tipps auf ein Formel 1 Rennausgang.

Das "Problem" das ich jetzt habe: Um diese Tipps mit den dazugehörigen Fahrer- und Benutzernamen auszulesen, muss ich 11 Inner Joins machen:

PHP-Code:

          $query = "select 

                    concat(LEFT(f1.vorname, 1), '. ', f1.nachname) as erster,

                    concat(LEFT(f2.vorname, 1), '. ', f2.nachname) as zweiter,

                    concat(LEFT(f3.vorname, 1), '. ', f3.nachname) as dritter,

                    concat(LEFT(f4.vorname, 1), '. ', f4.nachname) as vierter,

                    concat(LEFT(f5.vorname, 1), '. ', f5.nachname) as fuenfter,

                    concat(LEFT(f6.vorname, 1), '. ', f6.nachname) as sechster,

                    concat(LEFT(f7.vorname, 1), '. ', f7.nachname) as siebter,

                    concat(LEFT(f8.vorname, 1), '. ', f8.nachname) as achter,

                    concat(LEFT(fpp.vorname, 1), '. ', fpp.nachname) as poleposition,

                    concat(LEFT(fsr.vorname, 1), '. ', fsr.nachname) as s_runde,

                    b.nickname as nickname

                from tipps as t

                inner join fahrer as f1 on t.erster = f1.fahrer_id

                inner join fahrer as f2 on t.zweiter = f2.fahrer_id

                inner join fahrer as f3 on t.dritter = f3.fahrer_id

                inner join fahrer as f4 on t.vierter = f4.fahrer_id

                inner join fahrer as f5 on t.fuenfter = f5.fahrer_id

                inner join fahrer as f6 on t.sechster = f6.fahrer_id

                inner join fahrer as f7 on t.siebter = f7.fahrer_id

                inner join fahrer as f8 on t.achter = f8.fahrer_id

                inner join fahrer as fpp on t.poleposition = fpp.fahrer_id

                inner join fahrer as fsr on t.s_runde = fsr.fahrer_id

                inner join benutzer as b on t.benutzer_id = b.benutzer_id

                where t.rennkalender_id=".$rennkalenderID;

Mir erscheint das ganze aber nicht unbedingt ideal.
Hat jemand ne Idee wie man das besser lösen kann?

Danke

dr.e. · 14.03.2010, 19:08

Was sagt ein EXPLAIN auf dem Statement? Sofern die FK-Tabelle einen Primary-Index auf beiden Feldern hat, sollte das JOIN verdammt schnell sein.

14.03.2010, 19:16

Deine bisherige Tabelle hat einen Design-Fehler. Vermutlich sieht die Tabelle in etwa so aus.

Code:

CREATE TABLE tipps (
 user_id INT NOT NULL,
 rennen_id INT NOT NULL,
 fahrer1 INT NOT NULL,
 fahrer2 INT NOT NULL,
 ...
 fahrer10 INT NOT NULL,

 PRIMARY KEY (user_id)
)

Solche Spaltenwiederholungen wie "fahrer1" "fahrer2" etc. ist immer ein Indiz für einen Design-Fehler gemäß der Normalform. Dann bekommt man irgendwann Probleme bestimmte SQL-Abfragen "einfach" zu schreiben.

Ein besseres Design wäre Beispielweise so eine Tabelle

Code:

CREATE TABLE tipps_neu(
 user_id INT NOT NULL,
 rennen_id INT NOT NULL,
 fahrer_id INT NOT NULL,
 PRIMARY KEY (user_id)
)

Pro User und pro Rennen und pro Fahrer wird ein Datensatz angelegt. Dein Programm muss dann prüfen, dass genau 10 Datensätze pro user_id und rennen_id da sind. Deine obige Abfrage ist mit dieser Tabelle deutlich einfacher.

Mal als Anregung...

Grüße
Thomas

Spider · 14.03.2010, 20:23

Zitat:

Zitat von dr.e.

Was sagt ein EXPLAIN auf dem Statement?

Code:

12 rows in set (0.27 sec)

Meinst Du das ^^ ? Das ist das Ergebnis eines Explains.

@thomas_w:
Anstelle fahrer1, fahrer2, fahrer3.... etc zwar erster, zweiter, dritter... aber ja, so sieht meine Tabelle derzeit aus.

Code:

CREATE TABLE tipps_neu(
 user_id INT NOT NULL,
 rennen_id INT NOT NULL,
 fahrer_id INT NOT NULL,
 PRIMARY KEY (user_id)
)

Prinzipell leuchtet mir das ein. Aber so verliere ich die Zuordnung welchen Fahrer der User auf welche Positionen getippt hat.

14.03.2010, 21:01

Zitat:

Zitat von Spider

Code:

12 rows in set (0.27 sec)

dr.e hat die komplette Ausgabe des EXPLAIN gemeint.

Also die Ausgabe von

Code:

EXPLAIN SELECT ...

Das Ergebnis am Besten als Bild hier reinstellen, dann kann mal es gut lesen.

Zitat:

Zitat von Spider

@thomas_w:
Anstelle fahrer1, fahrer2, fahrer3.... etc zwar erster, zweiter, dritter... aber ja, so sieht meine Tabelle derzeit aus.

Code:

CREATE TABLE tipps_neu(
 user_id INT NOT NULL,
 rennen_id INT NOT NULL,
 fahrer_id INT NOT NULL,
 PRIMARY KEY (user_id)
)

Prinzipell leuchtet mir das ein. Aber so verliere ich die Zuordnung welchen Fahrer der User auf welche Positionen getippt hat.

Ja, es fehlt noch die Spalte "position_nr" in meiner Tabelle

Code:

CREATE TABLE tipps_neu(
 id INT NOT NULL,
 user_id INT NOT NULL,
 rennen_id INT NOT NULL,
 fahrer_id INT NOT NULL,
 position_nr INT NOT NULL,
 PRIMARY KEY (id)
)

So sollte es klappen. Position 1 - 10 und vielleicht 0 = Poleposition oder so. Wobei ich den Unterschied zwischen Position 1 und Poleposition nicht verstehe.

Grüße
Thomas

Spider · 15.03.2010, 09:36

Zitat:

Zitat von thomas_w

Wobei ich den Unterschied zwischen Position 1 und Poleposition nicht verstehe.

Es wird das Rennergebnis (Platz 1 bis

, die Poleposition und die schnellste Rennrunde getippt.

Der Explain den mir phpMyAdmin ausgibt:

hier klicken

(hab das Bild mal verlinkt, da es mit 970px recht breit ist)

15.03.2010, 10:07

Der EXPLAIN sieht eigentlich gut aus. MySQL findet viele passende Indices um die Abfrage performant ausführen zu können. Siehe Spalte "KEY". Die Anzahl der Zeilen in "ROWS" ist minimal. Da läßt sich nicht viel optimieren. Bleibt die generelle Fragen, ob Du das Datenmodell umstellen möchtest, falls Dir obiger SQL nicht gefällt (und das kann ich gut verstehen).

Meine überarbeitete Tabelle dazu:

Code:

CREATE TABLE tipps_neu(
 id INT NOT NULL,
 user_id INT NOT NULL,
 rennen_id INT NOT NULL,
 fahrer_id INT NOT NULL,
 position_nr INT NOT NULL,             //  getippte Position 1..x
 poleposition_id INT NOT NULL,        //  fahrer_id in der Poleposition
 schnellste_runde_id IN NOT NULL,    // fahrer_id der schnellsten Runde
 PRIMARY KEY (id)
)

EDIT
Nein, die Tabelle ist nicht gut so. poleposition und schnelleste_runde sind falsch..
Spontane Idee wäre

Code:

CREATE TABLE tipps_neu(
 id INT NOT NULL,
 user_id INT NOT NULL,
 rennen_id INT NOT NULL,
 fahrer_id INT NOT NULL,
 tipp_typ_id INT NOT NULL,             //  1..x getippte Position 1..x,   100 = Poleposition, 101 = schnelleste Runde
 PRIMARY KEY (id)
)

Grüße
Thomas

Spider · 15.03.2010, 17:29

Hmm, ich weis nicht.

Wie gesagt, prinzipell leutet mir das schon ein. Aber ich bin mir nicht wirklich sicher ob das so wirklich effektiv ist. Immerhin verzehnfacht diese Art die Anzahl Datensätze. Rechnen wir pauschal mal mit 200 Tippern, macht das 200 x 19 Rennen x 10 Datensätze = 38.000

Auf der anderen Seite steht halt diese große SQL-Wurst.

Ich werde das Local mal zusammen bauen und beide Abfrage mal durch den Apache Benchmark schicken. Mal schauen was dabei rauskommt.

Auf jedenfall erstmal Danke für Deine Ratschläge

15.03.2010, 18:30

Zitat:

Zitat von Spider

Wie gesagt, prinzipell leutet mir das schon ein. Aber ich bin mir nicht wirklich sicher ob das so wirklich effektiv ist. Immerhin verzehnfacht diese Art die Anzahl Datensätze. Rechnen wir pauschal mal mit 200 Tippern, macht das 200 x 19 Rennen x 10 Datensätze = 38.000

Gesetzt den Fall die bisherige Tabelle (rennen_1) und die neue Tabelle (rennen_2) sehen wie folgt aus, dann ergibt sich aber auch diese Rechnung:

Code:

CREATE TABLE rennen_2 (
 id INT NOT NULL,
 user_id INT NOT NULL,
 rennen_id INT NOT NULL,
 fahrer_id INT NOT NULL,
 tipp_typ_id INT NOT NULL,
 PRIMARY KEY(id)
);


CREATE TABLE rennen_1 (
 id INT NOT NULL,
 user_id INT NOT NULL,
 rennen_id INT NOT NULL,
 fahrer1 INT NOT NULL,
 fahrer2 INT NOT NULL,
 fahrer3 INT NOT NULL,
 fahrer4 INT NOT NULL,
 fahrer5 INT NOT NULL,
 fahrer6 INT NOT NULL,
 fahrer7 INT NOT NULL,
 fahrer8 INT NOT NULL,
 fahrer9 INT NOT NULL,
 fahrer10 INT NOT NULL, 
 polepos INT NOT NULL,
 schnellste_runde INT NOT NULL,  
 PRIMARY KEY(id)
);

INSERT INTO rennen_2 VALUES
(1 , 1, 1, 1, 1);

INSERT INTO rennen_1 VALUES
(1 , 1, 1, 1, 1, 
 1 , 1, 1, 1, 1,
 1 , 1, 1, 1, 1);

myslq>SHOW TABLE STATUS LIKE 'rennen%'

rennen_1 = avg_row_length = 63 
rennen_2 = avg_row_length = 21

200 x 19 x 63       = 239.400 Byte
200 x 19 x 12 x 21  = 957.600 Byte

Also ca. die 4-fache Datenmenge bei der Normalisierung.

Der größte Vorteil dürfte die Flexibilität sein. Wenn
also nicht nur Fahrer 1..10, Poleposition und schnellste Runde,
sondern vielleicht 8 Fahrer, Poleposition und die 2-schnellste Runde
getippt werden soll.

Aber probieren geht natürlich über studieren!

Viel Erfolg!

Grüße
Thomas

Spider · 15.03.2010, 20:15

Zitat:

Zitat von thomas_w

Der größte Vorteil dürfte die Flexibilität sein.

Das ist in der Tat ein schlagendes Argument.
Rein vom Benchmark her nehmen sich beide nichts.

Habe die Querys je 1000 mal in einer FOR-Schleife laufen lassen, bei jeweils 10.000 Scriptaufrufen.

Meine bisherige Tabellenkonstruktion brauchte dazu 32.613 Sekunden, die von thomas_w 32.108 Sekunden.
Beide InnoDB mit den entsprechenden Fremdschlüsseln und den jeweils gleichen Werten.

Da man ja flexibel bleiben möchte, werde ich dann wohl mal umstellen.

Themen-Optionen
Druckbare Version zeigen Jemanden per E-Mail auf dieses Thema hinweisen
Thema bewerten
Thema bewerten:

Ähnliche Themen
Thema	Autor	Forum	Antworten	Letzter Beitrag
[Erledigt] Intertabellarische Berechnungen, wie?	dave303	Datenbanken	13	05.03.2010 11:58
Referenzen von eine Tabelle in die andere Kopieren	wali	PHP Tipps 2009	3	13.01.2010 21:53
MY-SQL Abfrage nach Daten aus Tabelle 1 die in Tabelle 2 nicht vorhanden sind	triple81	Datenbanken	1	25.12.2009 22:46
Bestehende php /Myqsl Tabelle Neben einander aus geben	Totti-Totti	PHP Tipps 2009	3	21.12.2009 11:00
Bilder in Tabelle einfügen Mysql oder direkt?!	DKuhn	PHP Tipps 2009	3	30.09.2009 10:14
tabelle 2 in tabelle 1 updaten	steffen_dk	Datenbanken	11	04.07.2009 15:04
Zeilenanzahl einer tabelle mit WHERE aber ohne schleife?	sovereign	Datenbanken	13	17.04.2006 20:34
[Erledigt] Problem mit dem Füllen einer Tabelle über Formular		PHP Tipps 2006	18	10.01.2006 12:51
[Erledigt] Problem mit Anzeige einer Tabelle mit dem Firefox?		HTML, Usability und Barrierefreiheit	8	28.11.2005 15:08
Tabelle aktualisieren		Datenbanken	3	23.11.2005 09:54
Problem mit mySQL		Datenbanken	7	27.09.2005 12:06
mysql abfrage über 4 Tabelle - bis 3 geht, bei der 4. habert		Datenbanken	2	08.09.2005 11:59
tabelle in tabelle ohne aussenrand	noskule	HTML, Usability und Barrierefreiheit	6	25.08.2005 14:17
[Erledigt] Tabelle in Tabelle ausrichten		HTML, Usability und Barrierefreiheit	7	03.01.2005 14:32
[Erledigt] HILFE: Column count doesn't match value count at row 1		Datenbanken	17	12.06.2004 16:45

14.03.2010, 18:11
Spider Neuer Benutzer Registriert seit: 14.03.2010 Beiträge: 11 PHP-Kenntnisse: Fortgeschritten	Reine FK-Tabelle - Performant? Hallo, ich habe eine Tabelle in der 11 Werte gespeichert werden. Ein Wert ist die Benutzer-ID, die anderen 10 sind IDs auf eine Tabelle "fahrer", in der Fahrernamen gespeichert sind. Alle diese 11 Spalten sind Foreignkeys, einmal auf benutzer.benutzer_id und 10 mal auf fahrer.fahrer_id Das ganze sind Tipps auf ein Formel 1 Rennausgang. Das "Problem" das ich jetzt habe: Um diese Tipps mit den dazugehörigen Fahrer- und Benutzernamen auszulesen, muss ich 11 Inner Joins machen: PHP-Code: $query = "select concat(LEFT(f1.vorname, 1), '. ', f1.nachname) as erster, concat(LEFT(f2.vorname, 1), '. ', f2.nachname) as zweiter, concat(LEFT(f3.vorname, 1), '. ', f3.nachname) as dritter, concat(LEFT(f4.vorname, 1), '. ', f4.nachname) as vierter, concat(LEFT(f5.vorname, 1), '. ', f5.nachname) as fuenfter, concat(LEFT(f6.vorname, 1), '. ', f6.nachname) as sechster, concat(LEFT(f7.vorname, 1), '. ', f7.nachname) as siebter, concat(LEFT(f8.vorname, 1), '. ', f8.nachname) as achter, concat(LEFT(fpp.vorname, 1), '. ', fpp.nachname) as poleposition, concat(LEFT(fsr.vorname, 1), '. ', fsr.nachname) as s_runde, b.nickname as nickname from tipps as t inner join fahrer as f1 on t.erster = f1.fahrer_id inner join fahrer as f2 on t.zweiter = f2.fahrer_id inner join fahrer as f3 on t.dritter = f3.fahrer_id inner join fahrer as f4 on t.vierter = f4.fahrer_id inner join fahrer as f5 on t.fuenfter = f5.fahrer_id inner join fahrer as f6 on t.sechster = f6.fahrer_id inner join fahrer as f7 on t.siebter = f7.fahrer_id inner join fahrer as f8 on t.achter = f8.fahrer_id inner join fahrer as fpp on t.poleposition = fpp.fahrer_id inner join fahrer as fsr on t.s_runde = fsr.fahrer_id inner join benutzer as b on t.benutzer_id = b.benutzer_id where t.rennkalender_id=".$rennkalenderID; Mir erscheint das ganze aber nicht unbedingt ideal. Hat jemand ne Idee wie man das besser lösen kann? Danke


Mister Ad PHP Code Flüsterer Registriert seit: 21.08.2005 Beiträge: 4682 PHP-Kenntnisse: Fortgeschritten

14.03.2010, 19:08
dr.e. Moderator und Wett-König Registriert seit: 21.05.2008 Beiträge: 3.633 PHP-Kenntnisse: Fortgeschritten	Was sagt ein EXPLAIN auf dem Statement? Sofern die FK-Tabelle einen Primary-Index auf beiden Feldern hat, sollte das JOIN verdammt schnell sein. __________________ Viele Grüße, Dr.E. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 1. Think about software design before you start to write code! 2. Discuss and review it together with experts! 3. Choose good tools (-> Adventure PHP Framework (APF))! 4. Write clean and reusable software only! ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

14.03.2010, 19:16
thomas_w Gast Beiträge: n/a	Deine bisherige Tabelle hat einen Design-Fehler. Vermutlich sieht die Tabelle in etwa so aus. Code: CREATE TABLE tipps ( user_id INT NOT NULL, rennen_id INT NOT NULL, fahrer1 INT NOT NULL, fahrer2 INT NOT NULL, ... fahrer10 INT NOT NULL, PRIMARY KEY (user_id) ) Solche Spaltenwiederholungen wie "fahrer1" "fahrer2" etc. ist immer ein Indiz für einen Design-Fehler gemäß der Normalform. Dann bekommt man irgendwann Probleme bestimmte SQL-Abfragen "einfach" zu schreiben. Ein besseres Design wäre Beispielweise so eine Tabelle Code: CREATE TABLE tipps_neu( user_id INT NOT NULL, rennen_id INT NOT NULL, fahrer_id INT NOT NULL, PRIMARY KEY (user_id) ) Pro User und pro Rennen und pro Fahrer wird ein Datensatz angelegt. Dein Programm muss dann prüfen, dass genau 10 Datensätze pro user_id und rennen_id da sind. Deine obige Abfrage ist mit dieser Tabelle deutlich einfacher. Mal als Anregung... Grüße Thomas

15.03.2010, 10:07
thomas_w Gast Beiträge: n/a	Der EXPLAIN sieht eigentlich gut aus. MySQL findet viele passende Indices um die Abfrage performant ausführen zu können. Siehe Spalte "KEY". Die Anzahl der Zeilen in "ROWS" ist minimal. Da läßt sich nicht viel optimieren. Bleibt die generelle Fragen, ob Du das Datenmodell umstellen möchtest, falls Dir obiger SQL nicht gefällt (und das kann ich gut verstehen). Meine überarbeitete Tabelle dazu: Code: CREATE TABLE tipps_neu( id INT NOT NULL, user_id INT NOT NULL, rennen_id INT NOT NULL, fahrer_id INT NOT NULL, position_nr INT NOT NULL, // getippte Position 1..x poleposition_id INT NOT NULL, // fahrer_id in der Poleposition schnellste_runde_id IN NOT NULL, // fahrer_id der schnellsten Runde PRIMARY KEY (id) ) EDIT Nein, die Tabelle ist nicht gut so. poleposition und schnelleste_runde sind falsch.. Spontane Idee wäre Code: CREATE TABLE tipps_neu( id INT NOT NULL, user_id INT NOT NULL, rennen_id INT NOT NULL, fahrer_id INT NOT NULL, tipp_typ_id INT NOT NULL, // 1..x getippte Position 1..x, 100 = Poleposition, 101 = schnelleste Runde PRIMARY KEY (id) ) Grüße Thomas Geändert von thomas_w (15.03.2010 um 10:13 Uhr).

15.03.2010, 17:29
Spider Neuer Benutzer Registriert seit: 14.03.2010 Beiträge: 11 PHP-Kenntnisse: Fortgeschritten	Hmm, ich weis nicht. Wie gesagt, prinzipell leutet mir das schon ein. Aber ich bin mir nicht wirklich sicher ob das so wirklich effektiv ist. Immerhin verzehnfacht diese Art die Anzahl Datensätze. Rechnen wir pauschal mal mit 200 Tippern, macht das 200 x 19 Rennen x 10 Datensätze = 38.000 Auf der anderen Seite steht halt diese große SQL-Wurst. Ich werde das Local mal zusammen bauen und beide Abfrage mal durch den Apache Benchmark schicken. Mal schauen was dabei rauskommt. Auf jedenfall erstmal Danke für Deine Ratschläge