langsame viewforum.php (mysqlselect)

[hope]

Hi,

seit geraumer Zeit fällt mir auf das ein Query in viewforum.php (seite xxx) sehr lange brauch um Ergebnisse zu liefern:

Code: Alles auswählen

explain SELECT t.*, u.username, u.user_id, u2.username as user2, u2.user_id as id2, p.post_username, p2.post_username AS post_username2, p2.post_time, pt.post_text, pt.bbcode_uid
FROM phpbbboard_topics t, phpbbboard_users u, phpbbboard_posts p, phpbbboard_posts p2, phpbbboard_users u2, phpbbboard_posts_text pt
WHERE t.forum_id = 8
AND t.topic_poster = u.user_id
AND p.post_id = t.topic_first_post_id
AND p2.post_id = t.topic_last_post_id
AND p2.post_id = pt.post_id
AND u2.user_id = p2.poster_id
ORDER BY t.topic_type DESC, t.topic_last_post_id DESC LIMIT 12775, 25;

ergebnis vom explain:

Code: Alles auswählen

+----+-------------+-------+--------+-------------------+----------+---------+----------------------------------+-------+-----------------------------+
| id | select_type | table | type   | possible_keys     | key      | key_len | ref                              | rows  | Extra                       |
+----+-------------+-------+--------+-------------------+----------+---------+----------------------------------+-------+-----------------------------+
|  1 | SIMPLE      | t     | ref    | forum_id          | forum_id |       2 | const                            | 11169 | Using where; Using filesort |
|  1 | SIMPLE      | u     | eq_ref | PRIMARY           | PRIMARY  |       3 | db65681789.t.topic_poster        |     1 |                             |
|  1 | SIMPLE      | p     | eq_ref | PRIMARY           | PRIMARY  |       3 | db65681789.t.topic_first_post_id |     1 |                             |
|  1 | SIMPLE      | p2    | eq_ref | PRIMARY,poster_id | PRIMARY  |       3 | db65681789.t.topic_last_post_id  |     1 |                             |
|  1 | SIMPLE      | u2    | eq_ref | PRIMARY           | PRIMARY  |       3 | db65681789.p2.poster_id          |     1 |                             |
|  1 | SIMPLE      | pt    | eq_ref | PRIMARY           | PRIMARY  |       3 | db65681789.p2.post_id            |     1 |                             |
+----+-------------+-------+--------+-------------------+----------+---------+----------------------------------+-------+-----------------------------+

scheinbar nutzt mysql den index nicht oder ich interpretiere es falsch... auf jedenfall brauch das query über 20 sekunden und zwingt den server für diese zeit deutlich in die knie.

vielleicht hat jemand ne idee

Gruss

[hope]

Problem gelöst

Suche:

Code: Alles auswählen

$sql = "SELECT u.username, u.user_id, u.user_posts, u.user_from, u.user_website, u.user_email, u.user_icq, u.user_aim, u.user_yim, u.user_regdate, u.user_msnm, u.user_viewemail, u.user_rank, u.user_sig, u.user_sig_bbcode_uid, u.user_avatar, u.user_avatar_type, u.user_allowavatar, u.user_allowsmile, u.user_allow_viewonline, u.user_session_time, p.*,  pt.post_text, pt.post_subject, pt.bbcode_uid, m.longitude, m.latitude
        FROM " . POSTS_TABLE . " p, " . USERS_TABLE . " u LEFT JOIN " . MAP_MOD_USER_TABLE . " m ON u.user_id=m.user_id, " . POSTS_TEXT_TABLE . " pt
        WHERE   p.topic_id = $topic_id
                $limit_posts_time
                AND pt.post_id = p.post_id
                AND u.user_id = p.poster_id
        ORDER BY p.post_time $post_time_order

        LIMIT $start, ".(isset($finish)? ((($finish - $start) > 0)? ($finish - $start): -$finish): $board_config['posts_per_page']);

Ersetze durch:

Code: Alles auswählen

$p_array = array();
$sql = "SELECT p.post_id FROM " . POSTS_TABLE . " p WHERE p.topic_id = $topic_id $limit_posts_time LIMIT $start, " . $board_config['posts_per_page'];
if ( !($result = $db->sql_query($sql)) )
{
   message_die(GENERAL_ERROR, "Could not obtain post index information.", '', __LINE__, __FILE__, $sql);
}
while ( $row = $db->sql_fetchrow($result)) {
    $p_array[] = $row[post_id];
}
$post_index = implode(",",$p_array);

$sql = "SELECT u.username, u.user_id, u.user_posts, u.user_from, u.user_website, u.user_email, u.user_icq, u.user_aim, u.user_yim, u.user_regdate, u.user_msnm, u.user_viewemail, u.user_rank, u.user_sig, u.user_sig_bbcode_uid, u.user_avatar, u.user_avatar_type, u.user_allowavatar, u.user_allowsmile, u.user_allow_viewonline, u.user_session_time, p.*,  pt.post_text, pt.post_subject, pt.bbcode_uid, m.longitude, m.latitude
        FROM " . POSTS_TABLE . " p, " . USERS_TABLE . " u LEFT JOIN " . MAP_MOD_USER_TABLE . " m ON u.user_id=m.user_id, " . POSTS_TEXT_TABLE . " pt
        WHERE   p.post_id in ($post_index)
                AND pt.post_id = p.post_id
                AND u.user_id = p.poster_id
        ORDER BY p.post_time $post_time_order";

Bitte aufpassen, da ich den Map-Mod drinnen hab. Sollte nur verwandt werden wenn man sich mit php/mysql auskennt. Übernehme keine Garantie...