Mysql: index & key korrekt setzen

[toby]

hi!

hab ne tabelle, die festhällt, welche themen der user aboniert hat und mit welchen optionen.
also: id, userid, themenid, option1, option2

nun möchte ich die keys so setzen, dass immer nur ein datensatz mit identischer userid und themenid existieren darf. muss ich nun id, userid und themenid als keys setzen oder was?!

als nächstes soll noch das mit dem index sinnvoll gemacht werden:
es gibt 4 verschiedene arten der suche:
where userid=...
where themenid=...
where themenid=... and option1=...
where themenid=... and option2=...
alle werden ungefähr gleich häufig verwendet. bedeutet das, dass ich 4 indexe zu erstellen habe?
(es ist damit zu rechnen, dass die tabelle schon bald eine große menge an datensätzen erreichen wird, daher soll auch das keinen performanceverlust bedeuten)

danke für eure hilfe

[Snafu]

nun möchte ich die keys so setzen, dass immer nur ein datensatz mit identischer userid und themenid existieren darf. muss ich nun id, userid und themenid als keys setzen oder was?!

Im Script (ich nehme mal an, dass du etwas in PHP o.Ä. umsetzen willst)  zu verhindern, dass dies vorkommt wäre wohl am einfachsten. Das ist über eine simple Abfrage zu lösen [SELECT COUNT(id) FROM ... WHERE userid='xx' AND themenid='xx'].

als nächstes soll noch das mit dem index sinnvoll gemacht werden:
es gibt 4 verschiedene arten der suche:
where userid=...
where themenid=...
where themenid=... and option1=...
where themenid=... and option2=...

Und wo ist das Problem? Such doch einfach nach den Werten!

[toby]

das mit dem key hab ich so gelöst, dass über themenid und userid ein unique key drauf liegt. damit gehts bestens.

als nächstes soll noch das mit dem index sinnvoll gemacht werden:
es gibt 4 verschiedene arten der suche:
where userid=...
where themenid=...
where themenid=... and option1=...
where themenid=... and option2=...
Und wo ist das Problem? Such doch einfach nach den Werten!

wenn es zu größeren datenmengen kommt, wäre wohl ein index vorteilhaft.
weiß nur nicht, ob es so schlau ist, 4 indexe zu erstellen für die möglichen fälle oder ob es hierbei wirklich überflüssig ist.
danke für tipps

[Rouven]

Hi,

hab ne tabelle, die festhällt, welche themen der user aboniert hat und mit welchen optionen.
also: id, userid, themenid, option1, option2

nun möchte ich die keys so setzen, dass immer nur ein datensatz mit identischer userid und themenid existieren darf. muss ich nun id, userid und themenid als keys setzen oder was?!

Ich nehme mal an die ID ist der Primärschlüssel. Dementsprechend sollte er auch als solcher festgehalten werden (PRIMARY KEY).
Um userid und themenid zusätzlich unique zu machen setzt du einen weiteren Index:
CREATE UNIQUE INDEX ... (userid, themenid)
Die von Snafu vorgeschlagene Lösung der Prüfung im Skript sehe ich nur als Zusatz.

alle werden ungefähr gleich häufig verwendet. bedeutet das, dass ich 4 indexe zu erstellen habe?

Aus meiner heutigen Sicht (wobei bei Datenbanken und Indizes lernt man nie aus) würde ich sagen ja. Jeweils einen Index pro Suchschema.
Wie aber Ilja sicherlich im Laufe des Tages noch ergänzen wird: Probieren geht über studieren. Einen Index zu droppen oder zu setzen ist ja nun keine allzu schlimme Operation...

MfG
Rouven

[Ilja]

yo,

da mein name hier schon gefallen ist, will ich mich gleich an Rouven dranhängen....

was deine erste frage betrifft, so würde ich die spalte id aus der tabelle entfernen und gleich einen zusammengesetzten primary key über userid und themenid bilden. das ersparrt dir zusätzliche constraints, wie unique und not null. der primary key über die beiden spalten vereint diese beiden merkmale schon per definition und ist ein sehr nützlicher weg, um die datenkonsestenz sicherzustellen.

was das tuning betrifft, so ist ein index immer ein geignetes mittel, aber man sollte doch ein paar dinge beachten. leider ist das thema recht komplex und füllt leicht ganze bücher. aber ein paar kleine tips, wann ein index überhaupt erst sinn macht.

• die anzahl der datensätze, bei "kleinen" tabellen macht ein index oftmals keinen sinn, da ein full join nicht viel länger dauert als ein gesetzter index. wo genau die grenze liegt musst du einfach ausprobieren.

• eine weitere faustregel besagt, wenn die ergebnismenge 10% der gesamten datenmenge überschreitet, dann lohnt es sich nicht, einen index zu verwenden. sprich bekommst du zum beispiel als ergebnis die hälfte der tabelle zurück, macht ein index keinen sinn.

• die art der daten spielt eine rolle, sprich wie ist die kardenalität in den jeweiligen spalten. gleichen sich viele einträge, zum beispiel weil sehr oft berlin oder hamburg drinn steht, männlich oder weiblich, dann wird dir ein b-baum index nichts nützen. je nach dbms würde man dann einen binär-index einsetzen können.

du siehst, es gibt schon einige dinge zu beachten. letztlich ist es so, wie ROuven sagt, alles ausprobieren und sehen, ob ein index überhaupt benutzt wird, auch wenn er vorhanden ist und schauen, ob er zeit gewinnt.

falls du denn welche einsetzen willst, würde sich die anzahl deiner indexe auf drei beschränken lassen. einen über die userid, einen über themen und option1, den letzten über themenid und option2. entscheiden für das dbms ist die reihenfolge der indexe, sprich von links nach rechts. wenn du nur nach der themid abfragen willst, sprich ohne option1 oder option2, dann kann er trotzdem auf einen der beiden indexe zurückgreifen, weil die themid ganz links steht.

Ilja

[Ilja]

yo,

was das tuning betrifft, so ist ein index immer ein geignetes mittel

das sollte natürlich fast immer heißen, will mir ja nicht selber widersprechen...  ;-)

Ilja