[SQL allgemein] SQL Query / JOIN Problem

Einklappen
X
 
  • Filter
  • Zeit
  • Anzeigen
Alles löschen
neue Beiträge

  • [SQL allgemein] SQL Query / JOIN Problem

    So habe ein Problem mit meinem Forum...
    Habe ein Forensystem das ich mometan über mehrere Querys steuere,
    will das aber aus performancegründen auf eines pro Forumkategorie reduzieren.

    // Strukturdump unten!

    Wenn ich jetzt in einem Query alle Foren einer Kategorie abfragen will, auf die der User auch zugreifen darf hab ich folgendes Query:
    Code:
    SELECT 
    `boards`.`id`, 
    `boards`.`name`, 
    `boards`.`info`, 
    `boards`.`last_post`, 
    `boards`.`post_count`,
    
    count(distinct `threads`.`id`) as `t_anzahl`,
    count(distinct if(`new`.`time_stamp` IS NULL OR `new`.`time_stamp`<`postings`.`time_stamp`, `threads`.`id`, NULL)) 
    as `new_anzahl`,
    count(distinct if(`access`.`id` IS NOT NULL AND `member`.`id` IS NULL, NULL, 1)) as `zugriff`
    
    FROM `forum_kat` AS `kats`
    LEFT JOIN `forum_foren` AS `boards` ON `kats`.`id`=`boards`.`kat_id`
    LEFT JOIN `forum_thread` AS `threads` ON `boards`.`id`=`threads`.`forum_id`
    LEFT JOIN `forum_reply` AS `postings` ON `postings`.`thread_id`=`threads`.`id`
    LEFT JOIN `forum_new` AS `new` ON `new`.`user_id`=3 AND `new`.`thread_id`=`threads`.`id`
    LEFT JOIN `forum_group_foren_access` AS `access` ON `access`.`forum_id`=`boards`.`id`
    LEFT JOIN `forum_group_member` AS `member` ON `access`.`group_id`=`member`.`id` AND `member`.`user_id`='3'
    WHERE `kats`.`id`='2' GROUP by `boards`.`id`
    Hintergrundgedanken:
    der Rückgabewert "zugriff" zur Zeile des Forum X soll 1 enthalten wenn
    a) das Forum X keinen verknüpften "forum_group_foren_access"-Eintrag besitzt
    b) der zugreifende User über die Tabelle "forum_group_member" mit EINEM der zum Forum X verknüpften "forum_group_foren_access"-Einträge verknüpft ist.

    Problem dabei ist nun, dass beim LEFT JOIN mit der "forum_group_foren_access"-Tabelle nur der 1. Eintrag ausgewählt wird, der mit dem Forum verknüpft ist.
    Mit einem Forum können aber mehrere Einträge in dieser Tabelle verknüpft sein.

    Die Frage die sich stellt ist wie erreiche ich nun, dass in dem Statement jeder mit dem Forum verknüpfte Eintrag in der access Tabelle ausgewählt wird
    und überprüft wird ob zu diesem Eintrag auch ein Eintrag in der "forum_group_member"-Tabelle besteht.


    Code:
    # --------------------------------------------------------
    
    #
    # Tabellenstruktur für Tabelle `forum_group`
    #
    
    CREATE TABLE `forum_group` (
      `name` varchar(255) NOT NULL default '',
      `is_team` tinyint(4) NOT NULL default '1',
      `is_admin` tinyint(4) NOT NULL default '0',
      `is_mod` tinyint(4) NOT NULL default '0',
      `news_admin` tinyint(4) NOT NULL default '0',
      `book_admin` tinyint(4) NOT NULL default '0',
      `trial_admin` smallint(6) NOT NULL default '0',
      `poll_admin` smallint(6) NOT NULL default '0',
      `charts_admin` tinyint(4) NOT NULL default '0',
      `can_sp` tinyint(4) NOT NULL default '0',
      `order` tinyint(4) NOT NULL default '0',
      `id` int(11) NOT NULL auto_increment,
      PRIMARY KEY  (`id`)
    ) TYPE=MyISAM;
    
    # --------------------------------------------------------
    
    #
    # Tabellenstruktur für Tabelle `forum_group_foren_access`
    #
    
    CREATE TABLE `forum_group_foren_access` (
      `forum_id` int(11) NOT NULL default '0',
      `group_id` int(11) NOT NULL default '0',
      `id` int(11) NOT NULL auto_increment,
      PRIMARY KEY  (`id`)
    ) TYPE=MyISAM;
    
    # --------------------------------------------------------
    
    #
    # Tabellenstruktur für Tabelle `forum_group_member`
    #
    
    CREATE TABLE `forum_group_member` (
      `user_id` int(11) NOT NULL default '0',
      `group_id` int(11) NOT NULL default '0',
      `id` int(11) NOT NULL auto_increment,
      PRIMARY KEY  (`id`)
    ) TYPE=MyISAM;
    
    # --------------------------------------------------------
    
    #
    # Tabellenstruktur für Tabelle `forum_kat`
    #
    
    CREATE TABLE `forum_kat` (
      `name` varchar(255) NOT NULL default '',
      `ordnung` int(11) NOT NULL default '0',
      `id` int(11) NOT NULL auto_increment,
      PRIMARY KEY  (`id`)
    ) TYPE=MyISAM;
    
    # --------------------------------------------------------
    
    #
    # Tabellenstruktur für Tabelle `forum_new`
    #
    
    CREATE TABLE `forum_new` (
      `user_id` int(11) NOT NULL default '0',
      `thread_id` int(11) NOT NULL default '0',
      `time_stamp` int(11) NOT NULL default '0'
    ) TYPE=MyISAM;
    
    # --------------------------------------------------------
    
    #
    # Tabellenstruktur für Tabelle `forum_reply`
    #
    
    CREATE TABLE `forum_reply` (
      `user_id` int(11) NOT NULL default '0',
      `thread_id` int(11) NOT NULL default '0',
      `time_stamp` int(11) NOT NULL default '0',
      `text` text NOT NULL,
      `codes` int(11) NOT NULL default '0',
      `ip` varchar(255) NOT NULL default '0.0.0.0',
      `id` int(11) NOT NULL auto_increment,
      PRIMARY KEY  (`id`)
    ) TYPE=MyISAM;
    
    # --------------------------------------------------------
    
    #
    # Tabellenstruktur für Tabelle `forum_thread`
    #
    
    CREATE TABLE `forum_thread` (
      `name` varchar(255) NOT NULL default '',
      `user_id` int(11) NOT NULL default '0',
      `time_stamp` int(11) NOT NULL default '0',
      `forum_id` int(11) NOT NULL default '0',
      `last_reply_id` int(11) NOT NULL default '0',
      `close_id` int(11) NOT NULL default '0',
      `sticky` tinyint(4) NOT NULL default '0',
      `access_id` int(11) NOT NULL default '0',
      `lastpost_time` int(11) NOT NULL default '0',
      `id` int(11) NOT NULL auto_increment,
      PRIMARY KEY  (`id`)
    ) TYPE=MyISAM;
    
    # --------------------------------------------------------
    
    #
    # Tabellenstruktur für Tabelle `forum_user`
    #
    
    CREATE TABLE `forum_user` (
      `name` varchar(255) NOT NULL default '',
      ...
      `id` int(11) NOT NULL auto_increment,
      PRIMARY KEY  (`id`)
    ) TYPE=MyISAM;

  • #2
    PHP-Code:
    <?
    $query = mysql_query("SELECT `group_id` AS `id` FROM `forum_group_member` WHERE `user_id`='".$GLOBALS["user"]["id"]."'");
    if(mySQL_num_Rows($query) > 0){
        $menge = "";
        while($array = mySQL_fetch_Array($query)) $menge .= ",".$array["id"];
        $menge = "(".substr($menge, 1).")";
    }else $menge = "('')";
    if($GLOBALS["object"]["user"]->user_isin_admin_group($GLOBALS["user"]["id"])) $null = "1";
    else $null = "NULL";
    $query = mySQl_Query("SELECT 
    `boards`.`id`, 
    `boards`.`name`, 
    `boards`.`info`, 
    `boards`.`last_post`, 
    `boards`.`post_count`, 
    count(distinct `threads`.`id`) as `t_anzahl`, 
    count(distinct if(`new`.`time_stamp` IS NULL OR `new`.`time_stamp`<`postings`.`time_stamp`, `threads`.`id`, NULL)) as `new_anzahl`, 
    count(distinct if(`access`.`group_id` IS NOT NULL AND `access2`.`group_id` IS NULL AND `mod`.`user` IS NULL AND ".$null." IS NULL, NULL, 1)) as `zugriff` 
    FROM `forum_kat` AS `kats` 
    LEFT JOIN `forum_foren` AS `boards` ON `kats`.`id`=`boards`.`kat_id` LEFT JOIN `forum_thread` AS `threads` ON `boards`.`id`=`threads`.`forum_id` 
    LEFT JOIN `forum_reply` AS `postings` ON `postings`.`thread_id`=`threads`.`id` 
    LEFT JOIN `forum_new` AS `new` ON `new`.`user_id`='".$GLOBALS["user"]["id"]."' AND `new`.`thread_id`=`threads`.`id` 
    LEFT JOIN `forum_group_foren_access` AS `access` ON `access`.`forum_id`=`boards`.`id` 
    LEFT JOIN `forum_group_foren_access` AS `access2` ON `access2`.`forum_id`=`boards`.`id` AND `access2`.`group_id` IN ".$menge." 
    LEFT JOIN `forum_mods` AS `mod` ON `mod`.`forum`=`boards`.`id` AND `mod`.`user`='".$GLOBALS["user"]["id"]."' 
    WHERE `kats`.`id`='".$this->array[0]["id"]."' AND `boards`.`id` IS NOT NULL GROUP by `boards`.`id` ORDER BY `boards`.`ordnung`") or die(mysql_error());
    ?>
    so gehts... aber dem lahmen HosterSQL sei Dank dauert die abfrage 4-5 sec

    Kommentar


    • #3
      hast du mal ein EXPLAIN drüberlaufen lassen?
      stimmen die indizes?
      Ich denke, also bin ich. - Einige sind trotzdem...

      Kommentar


      • #4
        Code:
        table  		type  	possible_keys  	key  	key_len ref  	rows  	Extra 
         
        kats 		const	id 		id 	4 	const 	1 	Using temporary; Using filesort 
        boards 		ALL 	NULL 		NULL 	NULL 	NULL 	24 	where used 
        threads 	ALL 	NULL 		NULL 	NULL 	NULL 	221   
        postings 	ALL 	NULL 		NULL 	NULL 	NULL 	1977   
        new 		ALL 	NULL 		NULL 	NULL 	NULL 	4350   
        access 		ALL 	NULL 		NULL 	NULL 	NULL 	50   
        access2 	ALL 	NULL 		NULL	NULL 	NULL 	50   
        mod 		ALL 	NULL 		NULL 	NULL 	NULL 	3
        das kommt beim EXPLAIN der Abfrage rum

        Hab n paar indizies reingehaun:
        Code:
        table  		type  	possible_keys  		key	
        kats 		const 	id 			id 		 
        boards 		ref 	kat_id 			kat_id 		
        threads 	ref 	forum_id 		forum_id 	
        postings 	ref 	thread_id 		thread_id 	
        new 		ref 	thread_id,user_id 	thread_id 	
        access 		ref 	forum_id 		forum_id 	
        access2 	ref 	forum_id,group_id 	forum_id 	
        mod 		ALL 	forum,user 		NULL 		 
        ------------------------------------------------------------------------
        key_len  	ref  		rows  	Extra  
        4 		const 		1 	Using temporary; Using filesort
        4 		const 		8 	where used 
        4 		boards.id 	10   
        4 		threads.id 	9   
        4 		threads.id 	18 	where used 
        4 		boards.id 	4   
        4 		boards.id 	4   
        NULL 		NULL 		3
        Zuletzt geändert von MrDeluxe; 16.08.2004, 13:43.

        Kommentar

        Lädt...
        X