Tweet
hallo,
zunächst ein gruss an alle - dies ist mein erstes posting hier... ;-)
so, jetzt aber zu meinem problem. sorry, wenn ich das problem ausführlich beschreibe - aber dann wisst ihr halt gleich, worums geht...
ich hab eine mysql5-datenbank mit ca. 40 tabellen, von denen viele immer wieder über fremdschlüssel verknüpft werden müssen. um jetzt in der php-anwendung (CMS und frontend einer mit 6mio aufrufen frequentierten page) zentrale funktionen für anlegen und auslesen der foreign keys schreiben zu können, hab ich eine zentrale 'relations'-tabelle gemacht, die immer dann zwischen zwei tabellen sitzt, wenn diese per n:m-verbindung kombiniert werden müssen.
um die u.u. gleichen kombinationen von zwei tabellen in der relationen-tabelle unterscheiden zu können, hab ich eine alias-spalte erzeugt, die die art der verbindung beschreiben soll und an die abfragende methode übergeben wird.
die tabellenstruktur der tabelle sieht wie folgt aus:
id - bigint(20)
alias - varchar(30)
master_group_id - varchar(30)
master_content_id - int(11)
foreign_group_id - varchar(30)
foreign_content_id - int(11)
reverse - tinyint
wenn ich also jetzt für einen übergebenen datensatz die zugeordneten datensätze der anderen tabelle brauche, wird also der name der tabelle (group_id) und die id des ersten ('master')-datensatzes und der name der tabelle, mit den verknüpften datensätzen übergeben.
da ich aber nicht festlegen wollte, dass man nur in eine richtung suchen kann (ich will ja die verknüpften datensätze von beiden richtungen finden können), können also master-tabelle und master-id in der relationen-tabelle auch als foreign-datensatz gespeichert sein - ich muss also beide varianten abfragen.
da diese tabelle ständig abgefragt wird und die tabellen alle kreuz und quer verküpft sind, enhält diese auch sehr viele datensätze - derzeit etwa eine halbe mio.
mir ist klar, dass zur optimierung der performance die text-spalten in numerische typen geändert werden müssen. ist klar, wird auch passieren. das ändert aber nix daran, dass ich für diese tabelle eine optimale indizierung brauche.
hier noch kurz der query, wie er derzeit beim suchen der fremdschlüsselverknüpfungen aussieht:
ihr seht, dass die abfrage recht komplex ist. ich vermute auch, dass es an der indizierung liegt, dass mir der server gelegentlich bei einem seitenaufruf mit 99,9% cpu-last in die knie geht.
gesucht wird halt immer nach genau zwei kombinationen:
entweder
master_group_id, master_content_id, foreign_group_id, alias, reverse
oder
foreign_group_id, foreign_content_id, master_group_id, alias, reverse
das studium des mysql-handbuchs zu dem thema ist sehr, sehr umfrangreich und IMHO nicht immer leicht zu verstehen. vielleicht hat da jemand von euch mehr erfahrung, was die indizierung über mehrere spalten angeht, so wie es hier ja wohl der fall sein müsste.
vielleicht ist ja auch der query schon 'suboptimal' - und es hat jemand ne idee, wie man das besser machen könnte... *hoff*
ps: wenn ich die beiden zuletzt genannten spalten-kominationen als indizes anlege, mosert phpmyadmin, weil alias nicht in mehreren indizes vorkommen darf. also: ich komm grad nicht wirklich weiter...
vielen dank im voraus
gruss
hans
zunächst ein gruss an alle - dies ist mein erstes posting hier... ;-)
so, jetzt aber zu meinem problem. sorry, wenn ich das problem ausführlich beschreibe - aber dann wisst ihr halt gleich, worums geht...
ich hab eine mysql5-datenbank mit ca. 40 tabellen, von denen viele immer wieder über fremdschlüssel verknüpft werden müssen. um jetzt in der php-anwendung (CMS und frontend einer mit 6mio aufrufen frequentierten page) zentrale funktionen für anlegen und auslesen der foreign keys schreiben zu können, hab ich eine zentrale 'relations'-tabelle gemacht, die immer dann zwischen zwei tabellen sitzt, wenn diese per n:m-verbindung kombiniert werden müssen.
um die u.u. gleichen kombinationen von zwei tabellen in der relationen-tabelle unterscheiden zu können, hab ich eine alias-spalte erzeugt, die die art der verbindung beschreiben soll und an die abfragende methode übergeben wird.
die tabellenstruktur der tabelle sieht wie folgt aus:
id - bigint(20)
alias - varchar(30)
master_group_id - varchar(30)
master_content_id - int(11)
foreign_group_id - varchar(30)
foreign_content_id - int(11)
reverse - tinyint
wenn ich also jetzt für einen übergebenen datensatz die zugeordneten datensätze der anderen tabelle brauche, wird also der name der tabelle (group_id) und die id des ersten ('master')-datensatzes und der name der tabelle, mit den verknüpften datensätzen übergeben.
da ich aber nicht festlegen wollte, dass man nur in eine richtung suchen kann (ich will ja die verknüpften datensätze von beiden richtungen finden können), können also master-tabelle und master-id in der relationen-tabelle auch als foreign-datensatz gespeichert sein - ich muss also beide varianten abfragen.
da diese tabelle ständig abgefragt wird und die tabellen alle kreuz und quer verküpft sind, enhält diese auch sehr viele datensätze - derzeit etwa eine halbe mio.
mir ist klar, dass zur optimierung der performance die text-spalten in numerische typen geändert werden müssen. ist klar, wird auch passieren. das ändert aber nix daran, dass ich für diese tabelle eine optimale indizierung brauche.
hier noch kurz der query, wie er derzeit beim suchen der fremdschlüsselverknüpfungen aussieht:
PHP-Code:
public function get_fk_id_array($master_group_id, $master_content_id, $foreign_group_id, $alias, $reverse) {
global $modulogConf;
if($master_group_id != '' && $master_content_id != '' && $foreign_group_id != '' && $alias != '') {
$sql = "SELECT id, master_group_id, master_content_id, foreign_group_id, foreign_content_id
FROM modulogdb_relations WHERE reverse = '$reverse' AND alias = '$alias' AND ((master_group_id = '$master_group_id'
AND master_content_id = '$master_content_id' AND foreign_group_id = '$foreign_group_id'
AND foreign_content_id > 0) OR (master_group_id = '$foreign_group_id' AND foreign_group_id = '$master_group_id'
AND foreign_content_id = '$master_content_id' AND master_content_id > 0)) ORDER BY id";
$recordset = $this->conn->Execute($sql) or fehler($this->conn->ErrorMsg()."..."."(sql = $sql)", debug_backtrace());
...
gesucht wird halt immer nach genau zwei kombinationen:
entweder
master_group_id, master_content_id, foreign_group_id, alias, reverse
oder
foreign_group_id, foreign_content_id, master_group_id, alias, reverse
das studium des mysql-handbuchs zu dem thema ist sehr, sehr umfrangreich und IMHO nicht immer leicht zu verstehen. vielleicht hat da jemand von euch mehr erfahrung, was die indizierung über mehrere spalten angeht, so wie es hier ja wohl der fall sein müsste.
vielleicht ist ja auch der query schon 'suboptimal' - und es hat jemand ne idee, wie man das besser machen könnte... *hoff*
ps: wenn ich die beiden zuletzt genannten spalten-kominationen als indizes anlege, mosert phpmyadmin, weil alias nicht in mehreren indizes vorkommen darf. also: ich komm grad nicht wirklich weiter...
vielen dank im voraus
gruss
hans
Moderator
Kommentar