PHP/MySQL Datenbank/Tabellen fürs Suchen optimieren

D

d0ne

0
Hallo Leute,

ich habe jetzt mit PHP/MySQL angefangen und bin dabei eine Datenbank über Bücher aufzubauen.
Diese will ich durchsuchen können, nach Author, Tags, Ranking ...

Ich weiß, wie ich in der Datenbank suche, aber ich möchte nicht "nur" suchen, ich möchte schnell Suchen!
Wie baue ich also die Datenbank auf, damit ich sie schnellst möglich durchsuchen kann!?

Kann ich eine Tabelle (automatisch) nach dem Alphabet soriteren lasse, und dann sagen,
wenn jemand nach einem Bcuh mit "z" sucht "die sind ganz hinten also fange erst in Zeile XY an zu suchen!?

Oder was gibt es sonst für Tipps, um eine - aufs Suchen optimierte - Datenbank aufzubauen!?

Danke für jeden Tipp

mfg
d0ne

PS: Ihr dürft mich gerne zu google verweisen, sagt mir aber dann, nach was ich suchen soll - ich habe nocht nichts brauchbares/aktuelles gefunden. -.-
 
Hallo,
um solche Suchen in der Datenbankl schnell zu lösen gibt es sogenannte Indizes bzw. Schlüssel.
Jede Tabelle sollte über einen Primärschlüssel (nornalerweise ein fortlaufender numerischer Wert) verfügen, welche es möglich macht, einzelne Zeilen eindeutig zu identifizieren und die interne Sortierung vorgibt.
Des weiteren kannst du über beliebige Spalten weitere Indizes anlegen.
Ein Index erstellt ein weiteres Inhaltsverzeichnis über alle Werte dieser Spalten (normalerweise ein Suchbaum), welches das schnelle Selektieren nach diesen Spalten ermöglicht.

Angenommen, du hast für deine Buch-Datensätze die Spalten "Autor", "Titel" und "Erscheinungsjahr".
Dann kannst du deine Tabelle etwa so aufbauen:

Code: 
Spalte | Typ    | Index
-----------------------------
ID     | int    | PRIMARY KEY
autor  | text   | INDEX
titel  | text   | INDEX
jahr   | int(4) | -

Wenn du jetzt eine Query absetzt:
Code: 
SELECT autor, titel FROM buch WHERE autor LIKE 'Smith, %'
Dann werden alle Bücher gefunden, wo die Spalte autor mit "Smith, " anfängt. Da du einen Index auf dieser Spalte liegen hast, kann sich die Datenbank an diesem Index orientieren und findet schnell die nötigen Spalten.

Einfache Regel: Setze einen Index auf alle Spalten, die oft im "WHERE"- oder "ORDER BY"-Teil einer SQL-Abfrage vorkommen und viele verschiedene Werte beinhalten.
Es kann sein, dass MySQL keine Indizes auf Feldern von Typen mit variabler Länge erlaubt, wenn du dem Index nicht noch eine Länge zuweist.
Für Volltextsuchen hat MySQL auch den Index-Typ "FULLTEXT". Ich habe allerdings noch nicht damit gearbeitet und kann dazu nicht viel sagen.

Hier noch etwas Lektüre:
http://techblog.tilllate.com/2007/01/07/optimierung-von-mysql-abfragen-verwendung-des-index/

mfg, metax.
 
Original von d0ne
Kann ich eine Tabelle (automatisch) nach dem Alphabet soriteren lasse, und dann sagen,
wenn jemand nach einem Bcuh mit "z" sucht "die sind ganz hinten also fange erst in Zeile XY an zu suchen!?
Zum Vergrößern anklicken....

Um solche Sachen muss man sich eigentlich nicht selber kümmern. Sowas macht MySql selber. Du musst halt einen Index auf die jeweiligen Spalten legen.
Grundsätzlich solltest du beim Design der DB beachten, daß du nachher bei der Suche nicht zu viel Joinen musst. Joins kosten immer Zeit. Auch solltest du darauf achten daß du Zahlen auch als Zahlen speicherst und nicht aus Bequemlichkeit als String (varchar). Zahlen werden schneller gefunden als (Teil-)Strings.

Sonst fällt mit im Moment nichts mehr dazu ein.

Gruß odigo
 
Ok, erst einmal super (!), dass ich so schnell Hilfe bekommen habe! :)
Das mit dem Index habe ich mir mal grob angeguckt, werde ich mich auf jeden Fall noch intensiv einlesen!

Allerdings habe ich jetzt eine (wie es mir am Anfang schien) ziemlich chaotische Tabelle "Struktur" gefunden.
Ich nehme an, dass sich dabei jemand etwas gedacht hatte und ich wollte einfach mal fragen was ihr davon haltet, bzw. wie ihr die Geschwindigkeitsunterschiede zwischen Index und dieser ?Struktur? seht ...
Gerade weil du (odigo) einerseits sagst "Joins kosten immer Zeit" aber auch "Zahlen werden schneller gefunden als (Teil-)Strings?.

Ich versuche mal, die Struktur zu beschreiben:

Die Struktur ist, das ich eine Tabelle mit Buch_ID habe, wo
auch der Name,Länge sowie eine kurze Beschreibung des Buchs gespeichert sind.

Eine weitere Tabelle, in der jeweils ein Tag (z.B. "backen") und eine
dazugehörige Tag_ID stehen.
Eine weitere (3.)Tabelle verbindet die beiden IDs, also z.B. Buch_ID 1
gehört zu Tag_ID 5.
Da ein Hörbuch meist mehr als einen Tag hat, stehen entsprechend viele
Zeilen drin, also
Buch_ID 1 = Tag_ID 5;
Buch_ID 1 = Tag_ID 7;
Buch_ID 3 = Tag_ID 5;

Such ich nach einem Tag, wir in der Tag Tabelle nach der Tag_ID gesucht,
dann die dazugehörigen Buch_IDs in der dritten Tabelle gesucht,
und dann mit der Buch_ID in der ersten Tabelle der Name des Hörbuchs
gefunden.

Die Tabellen werden beim SQL Aufruf per (LEFT) JOIN (gibt es auch RIGTH
JOIN!?) verbunden.

So ist es dann auch für Autor aufgebaut...
(und wenn das so am schnellsten ist, müsste ich das ja auch für Gerne etc. machen - also viele Tabellen...)

wäre nett, wenn mir jemand sagen könnte, was ?besser? (bzw. vor allem schneller) ist. :)
(ich finde diese Struktur total unintuitiv und doof -.-)

mfg
d0ne
 
Original von d0ne
Such ich nach einem Tag, wir in der Tag Tabelle nach der Tag_ID gesucht,
dann die dazugehörigen Buch_IDs in der dritten Tabelle gesucht,
und dann mit der Buch_ID in der ersten Tabelle der Name des Hörbuchs
gefunden.
Zum Vergrößern anklicken....

Nein, so nicht. Da hab ich mich vielleicht etwas mißverständlich ausgedrückt. Bei n zu m-Beziehungen brauchst du natürlich die Zwischentabelle und da sollte man auch joinen. Drei Selects hintereinander kosten auf alle Fälle mehr Zeit als einer mit Joins.

An deiner Stelle würd ich mir um Performance nicht allzugroße sorgen machen. Ob jetzt ein Select 4 Millisekunden oder 7 Millisekunden braucht wirst du mit Sicherheit nicht merken (ausser du hast hunderte/tausende User gleichzeitig auf der DB). Du solltest nur schauen daß du beim Schreiben der Selects gerade mit like Full Table Scans vermeidest (sofern du wirklich einige Hunderttausend Datensätze hast).

Gruß odigo
 
odigo, sry und wahrscheinlich liegt es an mir, aber ich verstehe nciht genau, was du meinst!?

Du solltest nur schauen daß du beim Schreiben der Selects gerade mit like Full Table Scans vermeidest
Zum Vergrößern anklicken....
Und wenn ich jetzt eine Teblle erstelle, in der in der ersten Spalte "id" ist, in der zweiten "name", dritte "Autor" und vierte "Tags" (mehrere mit "$" von ein ander getrennt) und in der Suche ich nach den Tag "backen" dauert das doch sicher lange!? Wenn ich jetzt einen Index auf Tags (, Name und Autor) setzte, müsste es (deutlich) schneller gehen, richtig!?

Und jetzt zu meiner "Struktur" oben, ist diese schneller als meine (in diesem Post beschriebene) Tabelle (mit Index) oder langsamer - daraum geht es mir!?


mfg
d0ne

PS: Natürlich geht es mir nicht um 3 Millisekunden aber ich möchte nicht in 2 Wochen/Monaten merken, das meine Tebllen scheiße aufgebaut sind und alles neu machen - deshalb frage ich lieber jetzt.
 
Original von d0ne
Und wenn ich jetzt eine Teblle erstelle, in der in der ersten Spalte "id" ist, in der zweiten "name", dritte "Autor" und vierte "Tags" (mehrere mit "$" von ein ander getrennt) und in der Suche ich nach den Tag "backen" dauert das doch sicher lange!? Wenn ich jetzt einen Index auf Tags (, Name und Autor) setzte, müsste es (deutlich) schneller gehen, richtig!?
Zum Vergrößern anklicken....
Nein, hier bringt höchstens ein Volltext-Index etwas (dann müsstest du aber die MATCH-AGAINST-Syntax benutzen), da ein Index die Textfelder nur nach Anfang sortieren würde. Was du beschreibst ist genau die Art von Full-Table-Scan die du vermeiden solltest.
Ein Index auf Name und Autor bringt nur etwas, wenn du auch nach Name/Autor filterst oder sortierst.
Mach statt dessen für die Buch-zu-Tag-Zuordnung eine eigene Tabelle (anstatt das in einem mehrwertigen Textfeld dazustellen), dann kannst du einfach über die Tabellen joinen und hast keinen Full-Table-Scan, da die ID der Bücher ja sowieso im Primärschlüssel sind.
Eine zusätzliche Tabelle für die n-zu-m-Relation macht MySQL nichts aus, bringt dir aber einen entscheidenden Geschwindigkeitsvorteil.
Da die Einträge Buch-ID und Tag-ID in der neuen Tabelle eigentlich Fremdschlüssel auf die anderen Tabellen sind, solltest du diese Felder jeweils auch mit einem Index versehen. Dann kannst du schneller alle Bücher eines Tags bzw. alle Tags eines Buches selektieren.

mfg, metax.
 
Ok, ich habe vielleicht etwas weit ausgeholt. Ein Full Table Scan wird z.B. passieren wenn z.B. mit like '%acken' auf Tags suchst. Dann kann die DB ihren Index nicht mehr benutzen. Das kann man aber auch nicht verhindern (mir würde zumindest nicht einfallen wie). Vergiss das aber erstmal.

Du musst grundsätzlich auf alle Spalten in denen gesucht wird einen Index legen (z.B. Tag_Name) und auf die Spalten mit denen Tabellen mittels Join verknüpft werden (z.B. Tag_Id). Ob das MySQL eh automatisch macht kann ich nicht beurteilen. Musst halt schauen.

Ich denke dein momentaner Aufbau deiner Tabellen ist schon gar nicht so verkehrt. Ich denke da solltest du keine Performance-Probleme bekommen.

Solltest du dennoch Performance-Probleme bekommen (was ich nicht glaube) google mal nach MySql Explain. Damit lassen sich Selects analysieren.

Mit wie vielen Datensätzen rechnest du denn? Wieviele User sollen darauf arbeiten?

Gruß odigo

Edit:
mehrere mit "$" von ein ander getrennt
Zum Vergrößern anklicken....

... ist natürlich schmarrn. Machs mit Zwischentabelle.
 
momentaner Aufbau deiner Tabellen
Zum Vergrößern anklicken....
damit meinst du den in meinem zweiten Post erklärten Aufbau!?

Wenn ja, dann lass ich es so, bzw. baue so auf.

Ich danke allen für die Tipps/Erklärungen! :)
Auch wenn ich einiges nicht sofort gebrauchen kann - irgendwann bestimmt! ;)

mfg
d0ne

PS: es kann gut sein, dass ich nächster Zeit noch 'mal mir einer PHP/MySQL-Frage komme!
 
Um antworten zu können musst du eingeloggt sein.
Zurück
Oben