MYSQL-PHPMYADMIN Frage zu 2 Standard Tabellen

[schmidtedv]

Wohl ein alter Hut, aber immernoch aktuell. Warum ist im Attachment MOD und im Vanilla phpBB ein doppelter Index drin den phpMyAdmin immer bemängelt...

Code: Alles auswählen

Warnungen bei den Indizes der Tabelle `phpbb_attachments` 
Es sollte nicht mehr als ein Index des Typs INDEX für die Spalte `attach_id` gesetzt sein 
Warnungen bei den Indizes der Tabelle `phpbb_sessions` 
Die Index-Typen INDEX und PRIMARY sollten nicht gleichzeitig für die Spalte `session_id` gesetzt sein 

Code: Alles auswählen

CREATE TABLE phpbb_attachments (
  attach_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
  post_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
  privmsgs_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
  user_id_1 mediumint(8) NOT NULL,
  user_id_2 mediumint(8) NOT NULL,
  KEY attach_id_post_id (attach_id, post_id),
  KEY attach_id_privmsgs_id (attach_id, privmsgs_id),
  KEY post_id (post_id),
  KEY privmsgs_id (privmsgs_id)
) TYPE=MyISAM;

Code: Alles auswählen

CREATE TABLE phpbb_sessions (
   session_id char(32) DEFAULT '' NOT NULL,
   session_user_id mediumint(8) DEFAULT '0' NOT NULL,
   session_start int(11) DEFAULT '0' NOT NULL,
   session_time int(11) DEFAULT '0' NOT NULL,
   session_ip char(8) DEFAULT '0' NOT NULL,
   session_page int(11) DEFAULT '0' NOT NULL,
   session_topic int(11) DEFAULT '0' NOT NULL,
   session_logged_in tinyint(1) DEFAULT '0' NOT NULL,
   session_admin tinyint(2) DEFAULT '0' NOT NULL,
   PRIMARY KEY (session_id),
   KEY session_user_id (session_user_id),
   KEY session_id_ip_user_id (session_id, session_ip, session_user_id)
) TYPE=MyISAM;

Werden /sind diese doppelten Indexe genutzt / gebraucht oder sollte man die generell vermeiden? Warum sind Sie immernoch im Original-Code drin, wenn Sie, falls der Fall, unnütz sind?

[oxpus]

Ähm, ja, "Standard"-Tabellen sind diese beiden nicht, denn die gehören zu einen MOD und nicht zum phpBB

Und warum die immer noch drinnen sind, fragst Du besser den Autoren

Auf jeden Fall sind 2 Indizes auf ein und das selbe Feld ein Index zu viel.

[schmidtedv]

[quote="oxpus";p="72206"]Ähm, ja, "Standard"-Tabellen sind diese beiden nicht, denn die gehören zu einen MOD und nicht zum phpBB

Und warum die immer noch drinnen sind, fragst Du besser den Autoren

Auf jeden Fall sind 2 Indizes auf ein und das selbe Feld ein Index zu viel.[/quote]


Die sessins ist so dem Original entnommen und immernoch somit fehlerhaft....oder?

Bzw. was könnte denn aus diesen 2 Strukturen wegfallen, damit es nicht mehr stört aber immernoch fehlerfrei läuft?

Aus der Sessions nehme ich mal an session_user_id aus

KEY session_id_ip_user_id (session_id, session_ip, session_user_id)

und aus der attachments?

[oxpus]

Ja, was eben doppelt ist...

[schmidtedv]

...nur um das richtig zu verstehen...Bei

Code: Alles auswählen

CREATE TABLE phpbb_attachments (
  attach_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
  post_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
  privmsgs_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
  user_id_1 mediumint(8) NOT NULL,
  user_id_2 mediumint(8) NOT NULL,
  KEY attach_id_post_id (attach_id, post_id),
  KEY attach_id_privmsgs_id (attach_id, privmsgs_id),
  KEY post_id (post_id),
  KEY privmsgs_id (privmsgs_id)
) TYPE=MyISAM;

...wäre hier KEY attach_id_privmsgs_id (attach_id, privmsgs_id) überflüssig? Oder müßte ich irgendwo kontrollieren, ob der Index genutzt wird?

[oxpus]

Wie wäre es einfach mal mit dieser Variante:

Code: Alles auswählen

CREATE TABLE phpbb_attachments (
  attach_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
  post_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
  privmsgs_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
  user_id_1 mediumint(8) NOT NULL,
  user_id_2 mediumint(8) NOT NULL,
  KEY attach_id (attach_id, post_id, privmsgs_id),
);

[schmidtedv]

Klingt gut, wenn ich auch nicht wirklich weiß, wozu hier dann überhaupt so viele Indexe verbaut wurden...kann ich irgendwie nach den eigens erstellten Indexen in der forenstruktur suchen um zu sehen ob so ein extra exotischer Index wie attach_id_privmsgs_id überhaubt benutzt wird, will sagen, kann ich da den Rest meiner mysql_schema.sql mal nach unnützen Indexen durchforsten, oder ist das nicht so einfach möglich?

Ich nehme mal zudem an, wenn ich das nun beginne etwas zu verstehen, das ein Primary Key nur nötig wäre, wenn es ein auto_incremet in der Tabelle gibt?

Könnte ich also (was ist denn überhaubt der Unterschied zwischen Promary und Normal in diesem Fall) z.B. das folgende statt mittels Primary Key auch mittels Key lösen...oder ist das egal?

Code: Alles auswählen

CREATE TABLE phpbb_attachments_config (
  config_name varchar(255) NOT NULL,
  config_value varchar(255) NOT NULL,
  PRIMARY KEY (config_name)
) TYPE=MyISAM;

Bzw. nochmal zur sessions-Tabelle...müßte die dann nicht nach Adam Riese statt so:

Code: Alles auswählen

CREATE TABLE phpbb_sessions (
   session_id char(32) DEFAULT '' NOT NULL,
   session_user_id mediumint(8) DEFAULT '0' NOT NULL,
   session_start int(11) DEFAULT '0' NOT NULL,
   session_time int(11) DEFAULT '0' NOT NULL,
   session_ip char(8) DEFAULT '0' NOT NULL,
   session_page int(11) DEFAULT '0' NOT NULL,
   session_topic int(11) DEFAULT '0' NOT NULL,
   session_logged_in tinyint(1) DEFAULT '0' NOT NULL,
   session_admin tinyint(2) DEFAULT '0' NOT NULL,
   PRIMARY KEY (session_id),
   KEY session_user_id (session_user_id),
   KEY session_id_ip_user_id (session_id, session_ip)
) TYPE=MyISAM;

...so lauten:

Code: Alles auswählen

CREATE TABLE phpbb_sessions (
   session_id char(32) DEFAULT '' NOT NULL,
   session_user_id mediumint(8) DEFAULT '0' NOT NULL,
   session_start int(11) DEFAULT '0' NOT NULL,
   session_time int(11) DEFAULT '0' NOT NULL,
   session_ip char(8) DEFAULT '0' NOT NULL,
   session_page int(11) DEFAULT '0' NOT NULL,
   session_topic int(11) DEFAULT '0' NOT NULL,
   session_logged_in tinyint(1) DEFAULT '0' NOT NULL,
   session_admin tinyint(2) DEFAULT '0' NOT NULL,
   KEY (session_id, session_ip),
   KEY session_user_id (session_user_id)
) TYPE=MyISAM;

...also PRIMARY raus da kein auto_incremet und zudem session_ip nach oben, da session_id_ip_user_id bei mir in keiner Abfrage im forum auftaucht...(nach Suche in allen Dateien)? Oder einfach mit PRIMARY um da nicht dran rumzupfuschen, als PRIMARY KEY (session_id, session_ip)?


Noch ein Gedanke, der falsch sein mag...ein Index dient der schnelleren Abfragen-Findung bzw. Sortierung? Wenn dem so ist, wäre da nicht in

Code: Alles auswählen

CREATE TABLE phpbb_forums (
   forum_id smallint(5) UNSIGNED NOT NULL,
   cat_id mediumint(8) UNSIGNED NOT NULL,
   forum_name varchar(150),
   forum_desc text,
   forum_rules text NOT NULL,
   forum_status tinyint(4) DEFAULT '0' NOT NULL,
   forum_order mediumint(8) UNSIGNED DEFAULT '1' NOT NULL,
   forum_posts mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
   forum_topics mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
   forum_views mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
   forum_last_post_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
   forum_notify tinyint(1) UNSIGNED DEFAULT '1' NOT NULL,
   prune_next int(11),
   prune_enable tinyint(1) DEFAULT '0' NOT NULL,
   rules_display_title tinyint(1) DEFAULT '1' NOT NULL,
   rules_custom_title varchar(80) NOT NULL,
   rules_in_viewforum tinyint(1) UNSIGNED DEFAULT '0' NOT NULL,
   rules_in_viewtopic tinyint(1) UNSIGNED DEFAULT '0' NOT NULL,
   rules_in_posting tinyint(1) UNSIGNED DEFAULT '0' NOT NULL,
   auth_view tinyint(2) DEFAULT '0' NOT NULL,
   auth_read tinyint(2) DEFAULT '0' NOT NULL,
   auth_post tinyint(2) DEFAULT '0' NOT NULL,
   auth_reply tinyint(2) DEFAULT '0' NOT NULL,
   auth_edit tinyint(2) DEFAULT '0' NOT NULL,
   auth_delete tinyint(2) DEFAULT '0' NOT NULL,
   auth_sticky tinyint(2) DEFAULT '0' NOT NULL,
   auth_announce tinyint(2) DEFAULT '0' NOT NULL,
   auth_vote tinyint(2) DEFAULT '0' NOT NULL,
   auth_pollcreate tinyint(2) DEFAULT '0' NOT NULL,
   auth_attachments tinyint(2) DEFAULT '0' NOT NULL,
   auth_download tinyint(2) DEFAULT '0' NOT NULL,
   sort_attach_field varchar(50) DEFAULT 'filetime' NOT NULL,
   sort_attach_order varchar(5) DEFAULT 'DESC' NOT NULL,
   image_ever_thumb tinyint(1) DEFAULT '0' NOT NULL,
   PRIMARY KEY (forum_id),
   KEY forums_order (forum_order),
   KEY cat_id (cat_id),
   KEY forum_last_post_id (forum_last_post_id)
) TYPE=MyISAM;

ein Index auf forum_status noch angebracht? so wäre bei vielen foren in der viewforum.php z.B. doch ein Aufbau schneller...oder bin ich hier auf dem spekulativen Holzweg?


...und zu guter Letzt wäre hier:

Code: Alles auswählen

CREATE TABLE phpbb_banlist (
   ban_id mediumint(8) UNSIGNED NOT NULL auto_increment,
   ban_userid mediumint(8) DEFAULT '0' NOT NULL,
   ban_ip char(8) DEFAULT '' NOT NULL,
   ban_email varchar(255) DEFAULT NULL,
   PRIMARY KEY (ban_id),
   KEY ban_ip_user_id (ban_ip, ban_userid)
) TYPE=MyISAM;

nicht besser:

Code: Alles auswählen

CREATE TABLE phpbb_banlist (
   ban_id mediumint(8) UNSIGNED NOT NULL auto_increment,
   ban_userid mediumint(8) DEFAULT '0' NOT NULL,
   ban_ip char(8) DEFAULT '' NOT NULL,
   ban_email varchar(255) DEFAULT NULL,
   PRIMARY KEY (ban_id),
   KEY ban_userid (ban_userid, ban_ip)
) TYPE=MyISAM;

....da ich auch hier wieder kein ban_ip_user_id finden kann, bzw. ban_userid ja eh Indiziert wird...


Der Grund, warum ich Frage ist diese Anzeige in phpMyAdmin:

Code: Alles auswählen

Select_full_join 123   Anzahl der Joins ohne Schlüssel. Wenn dieser Wert nicht 0 ist sollten die Indizes der Tabellen sorgfältig überprüft werden.  

Das muß nicht an phpBB liegen, da hier auch die DB für ISPConfig (meine Server-Software) drin steckt und die hat viele doppelte Indexe zwecks abwärtskompatibilität zu mysql 3 drin...aber ich dachte, ich schau und frag mal hier nach, wo ich doch immer gerne verbessere aber wirklich kein genie in diesen dingen bin (eher gern durchs fragen dazulerne :-) )

[oxpus]

Oha, denkt jemand sehr komplex.

Zunächst:
Der wichtigste Unterschied zwischen PRIMARY KEY und KEY liegt in der Wiederholbarkeit der Einträge. Während ein PRIMARY KEY nur eindeutige Werte beinhalten darf (muss kein AUTO_INCREMENT-Feld sein!), dürfen sich in KEY-Feldern Datenwerte wiederholen.
Damit eignen sich der PRIMARY KEY auch idealerweise für ID's, die den betreffenden Datensatz eindeutig kennzeichnen, während die "normalen" KEY's für andere, für die Tabelle wichtige Datenfelder gedacht sind.

Soweit zum Unterschied zwischen den beiden Index-Arten.

Des weiteren ist der Index-Name nur Schall und Rauch, da er in der Benutzung der Tabellen direkt nicht verwendet wird.
Die Datenbank braucht aber einen Bezeichner, damit der Index auch verwaltet werden kann, daher der Name.
Also sind PRIMARY KEY und KEY immer mit einen Namen anzugeben, der aber frei vergeben werden kann!

Doppelte Indizes auf ein oder mehrere Felder sind dagegen zwar nicht falsch, aber komplett unnötig.
Ein Index soll ja nur dafür sorgen, daß z. B. bei Bedingungen auf die so definierten Schlüsselfelder die Ergebnisse aus der Tabelle schneller geliefert werden können.
Ein mehrfacher Index auf ein Feld würde da also mindestens einer zu viel sein und kann sogar die Tabelle eher verlangsamen, als es was nutzt.

Kurzum:
Setze PRIMARY KEY's da ein, wo z. B. eine Datensatz-ID eindeutig ist (wie die Post-ID in der Posts-Tabelle), einen KEY dazu aber auch nur dann, wenn die so angelegten Indizes auch wirklich in Abfragebedingungen genutzt werden.
Ein guter Modder berücksichtigt das bereits.
Jeder ungenutzte Index belegt nur unnötig Platz in der Datenbank und den möchte man ja doch wohl eher klein halten.
Und solange die Datenbank/phpmyadmin nicht doppelte oder gegensätzliche Indizes anzeigt/anmeckert, lass einfach die Finger davon, denn ein vorschnell gelöschter Index verlangsamt sofort auch die betreffende Tabelle

[schmidtedv]

Demnach sollte die sessions so aussehen? :-)

Code: Alles auswählen

CREATE TABLE phpbb_sessions ( 
   session_id char(32) DEFAULT '' NOT NULL, 
   session_user_id mediumint(8) DEFAULT '0' NOT NULL, 
   session_start int(11) DEFAULT '0' NOT NULL, 
   session_time int(11) DEFAULT '0' NOT NULL, 
   session_ip char(8) DEFAULT '0' NOT NULL, 
   session_page int(11) DEFAULT '0' NOT NULL, 
   session_topic int(11) DEFAULT '0' NOT NULL, 
   session_logged_in tinyint(1) DEFAULT '0' NOT NULL, 
   session_admin tinyint(2) DEFAULT '0' NOT NULL, 
   PRIMARY KEY (session_id), 
   KEY session_user_id (session_user_id), 
   KEY session_ip (session_ip) 
) TYPE=MyISAM;

bzw den forum_status habe ich als index noch in die phpbb_forums hinzugefügt...nun hat phpMyAdmin hier allerdings andere Kardinalitäten vergeben...ist das schlimm oder egal?

[oxpus]

Wäre so eine Möglichkeit, ja.

[schmidtedv]

:-) ich hab's mal getraut zu verbauen und schau's mir gleich mal testweise durch....


Sieht gut aus...auch einige INDEX-Namen geändert...stört das Board bisher nicht...auch die Anpassung der phpbb_sessions hat alles mitgemacht...dennoch hab ich letztlich noch den DB Maintenance MOD einmal drübergejagt...

[oxpus]

Nochmal:
Die Indize-Namen sind für das Board und für PHP völlig belanglos, da diese nirgends direkt eine Rolle spielen.
Daher ist es egal, ob ein Index "Lieschen_Mueller" oder "post_id" heisst...

[schmidtedv]

ja, schon recht...dient in dem Schritt ja nur der Übersicht...die anderen Änderungen waren mir wichtig, um hier die phpMyAdmin-Meldung wegzukriegen bzw. phpBB nicht für unnütze Indizes verantwortlich zu machen...so kann ich den mysql-Flaschenhals in den anderen DB's suchen