[php,sql] c-plusplus.de forum index schneller machen

kann man performancekritische Sachen nicht in C++ schreiben?

Marc++us

Die SQL-Abfrage ist zeitkritisch, und mySQL ist doch in C++ geschrieben.

Ein Join über soviele Tabellen dauert... egal ob VB, C++ oder PHP.

Shade Of Mine

Marc++us schrieb:

Ein Join über soviele Tabellen dauert...

eben
deshalb sehe ich keine andere moeglichkeit als die tabellenstruktur zu aendern

Gerard

ich habe es jetzt so gelöst

default:
			$sql = "SELECT f.*, p.post_time, p.post_username, u.username, u.user_id
				FROM (( " . FORUMS_TABLE . " f
				LEFT JOIN " . POSTS_TABLE . " p ON p.post_id = f.forum_last_post_id )
				LEFT JOIN " . USERS_TABLE . " u ON u.user_id = p.poster_id )
				ORDER BY f.cat_id, f.forum_order";
			break;
	}
	if ( !($result = $db->sql_query($sql)) )
	{
		message_die(GENERAL_ERROR, 'Could not query forums information', '', __LINE__, __FILE__, $sql);
	}

	$forum_data = array();
	while( $row = $db->sql_fetchrow($result) )
	{
		$forum_data[] = $row;
	}

	if ( !($total_forums = count($forum_data)) )
	{
		message_die(GENERAL_MESSAGE, $lang['No_forums']);
	}

//Dimah

function gecachter_last_topic_on_index()
{
    static  $topic_data = array();
    global  $forum_data, $total_forums, $db;

    $orig_word = array();
    $replacement_word = array();
    obtain_word_list($orig_word, $replacement_word);

    for($i = 0; $i < $total_forums; $i++)
    {
        if ( $forum_data[$i]['cat_id'] == 7)
        {
            $topic_data[$i]['topic_title_full'] = "&nbsp;Archiv - hier kann nur gelesen werden";
            $topic_data[$i]['topic_title_short'] = "&nbsp;Archiv - hier kann nur gelesen werden";
        }
        else if($topic_data[$i]['id'] != $forum_data[$i]['forum_last_post_id'])
        {
            $sql = "SELECT p.topic_id FROM ".POSTS_TABLE." p WHERE p.post_id = ".$forum_data[$i]['forum_last_post_id'];
            if ( !($result = $db->sql_query($sql)) )
            {
                message_die(GENERAL_ERROR, 'Could not query forums information', '', __LINE__, __FILE__, $sql);
            }
            $row = $db->sql_fetchrow($result);

            $sql = "SELECT t.topic_title FROM ".TOPICS_TABLE ." t WHERE t.topic_id = ".$row['topic_id'];
            if ( !($result = $db->sql_query($sql)) )
            {
                message_die(GENERAL_ERROR, 'Could not query forums information', '', __LINE__, __FILE__, $sql);
            }
            $row = $db->sql_fetchrow($result);

            if (count($orig_word))
                $row['topic_title'] = preg_replace($orig_word, $replacement_word, $row['topic_title']);

            $topic_data[$i]['topic_title_full']  = $row['topic_title'];
            $topic_data[$i]['id']                = $forum_data[$i]['forum_last_post_id'];

            if (strlen($row['topic_title']) > 43) 
            {
                $topic_data[$i]['topic_title_short'] = substr($row['topic_title'],0,40) . '...';
            }
            else
            {
                $topic_data[$i]['topic_title_short'] = $row['topic_title'];
            }

        }

        $forum_data[$i]['topic_title_full'] = $topic_data[$i]['topic_title_full'];
        $forum_data[$i]['topic_title_short'] = $topic_data[$i]['topic_title_short'];
    }
}
        gecachter_last_topic_on_index();

/*	
	//
	// Filter topic_title not allowed to read
	// 

	if ( !($userdata['user_level'] == ADMIN && $userdata['session_logged_in']) ) {
		$auth_read_all = array();
		$auth_read_all = auth(AUTH_READ, AUTH_LIST_ALL, $userdata, $forum_data);
		$auth_data = '';
		for($i=0; $i<count($forum_data); $i++)
		{
			if (!$auth_read_all[$forum_data[$i]['forum_id']]['auth_read']) 
			{
				$forum_data[$i]['topic_title']='';
			}
		}
	}
*/
//end of Dimah

und etwas weiter unten

//Dimah
//Orginal
/*

							if ( $forum_data[$j]['forum_last_post_id'] )
							{
								$last_post_time = create_date($board_config['default_dateformat'], $forum_data[$j]['post_time'], $board_config['board_timezone']);

								$last_post = $last_post_time . '<br />';

								$last_post .= ( $forum_data[$j]['user_id'] == ANONYMOUS ) ? ( ($forum_data[$j]['post_username'] != '' ) ? $forum_data[$j]['post_username'] . ' ' : $lang['Guest'] . ' ' ) : '<a href="' . append_sid("profile.$phpEx?mode=viewprofile&amp;" . POST_USERS_URL . '='  . $forum_data[$j]['user_id']) . '">' . $forum_data[$j]['username'] . '</a> ';

								$last_post .= '<a href="' . append_sid("viewtopic.$phpEx?"  . POST_POST_URL . '=' . $forum_data[$j]['forum_last_post_id']) . '#' . $forum_data[$j]['forum_last_post_id'] . '"><img src="' . $images['icon_latest_reply'] . '" border="0" alt="' . $lang['View_latest_post'] . '" title="' . $lang['View_latest_post'] . '" /></a>';
							}
							else
							{
								$last_post = $lang['No_Posts'];
							}
*/
//end of Orginal

                                     if ($forum_data[$j]['forum_last_post_id'] )
                                     {
                                        $last_post_time = create_date($board_config['default_dateformat'], $forum_data[$j]['post_time'], $board_config['board_timezone']);

                                        $last_post = '&nbsp;'.$last_post_time.'<br />&nbsp;<a href="' . append_sid("viewtopic.$phpEx?"  . POST_POST_URL . '=' . $forum_data[$j]['forum_last_post_id']) . '#' . $forum_data[$j]['forum_last_post_id'] . '" title="' . $forum_data[$j]['topic_title_full'] . '">' . $forum_data[$j]['topic_title_short'] . '</a> &nbsp;(';
                                        $last_post .= ( $forum_data[$j]['user_id'] == ANONYMOUS ) ? ( ($forum_data[$j]['post_username'] != '' ) ? $forum_data[$j]['post_username'] . ' ' : $lang['Guest'] . ' ' ) : '<a href="' . append_sid("profile.$phpEx?mode=viewprofile&amp;" . POST_USERS_URL . '='  . $forum_data[$j]['user_id']) . '">' . $forum_data[$j]['username'] . '</a>';
                                        $last_post .= ')&nbsp;&nbsp;';
                                     }
                                     else
                                     {
                                        $last_post = $lang['No_Posts'];
                                     }

//end of Dimah

was kann ich noch optimieren?

Gerard

Peinlich aber wahr, mein code cacht granicht die topic titel.
da php die static, global varibalen nur solange am leben erhält bis das script durch ist

der speed gewinn kommt duch den verzicht der JOINS zustannde