Ok,
ich scheine damit die Version gefunden zu haben, die funktioniert:
Code: Alles auswählen
//
// Go ahead and pull all data for this topic
//
//-- mod : speed up topic ------------------------------------------------------
//-- delete
/*
$sql = "SELECT u.username, u.user_id, u.user_posts, u.user_from, u.user_firstname, u.user_ebayname, u.user_car, 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, p.*, pt.post_text, pt.post_subject, pt.bbcode_uid
FROM " . POSTS_TABLE . " p, " . USERS_TABLE . " u, " . 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 ASC
LIMIT $start, ".$board_config['posts_per_page'];
*/
/*
$sql = "SELECT pt.post_sub_title, u.username, u.user_id, u.user_posts, u.user_from, u.user_firstname, u.user_ebayname, u.user_car, 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_gender, p.*, pt.post_text, pt.post_subject, pt.bbcode_uid
FROM " . POSTS_TABLE . " p, " . USERS_TABLE . " u, " . 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 ASC
LIMIT $start, ".$board_config['posts_per_page'];
*/
//-- add
// Pages
$total_pages = ceil($total_replies/$board_config['posts_per_page']);
$on_page = floor($start / $board_config['posts_per_page']) + 1;
if ($start > 100 && ($total_replies / 2) < $start) {
$reverse = TRUE;
$last_page_posts = $total_replies - ($board_config['posts_per_page'] * ($total_pages - 1));
}
// Reverse & Post Id's
$p_array = array();
if (isset($reverse)) {
$limit_string = ($total_pages == $on_page) ? $last_page_posts : ($last_page_posts + ($total_pages - $on_page - 1) * $board_config['posts_per_page'] ).','. $board_config['posts_per_page'];
$sql = "SELECT p.post_id FROM " . POSTS_TABLE . " p USE INDEX(topic_n_id) WHERE p.topic_id = $topic_id $limit_posts_time ORDER BY p.post_id DESC LIMIT $limit_string" ;
} else {
$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 (list($p_id) = $db->sql_fetchrow($result)) {
$p_array[] = $p_id;
}
$post_index = implode(",",$p_array);
// Pull the data
$sql = "SELECT pt.post_sub_title, u.username, u.user_id, u.user_posts, u.user_from, u.user_firstname, u.user_ebayname, u.user_car, 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_gender, p.*, pt.post_text, pt.post_subject, pt.bbcode_uid
FROM " . POSTS_TABLE . " p, " . USERS_TABLE . " u, " . 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";
if ( !($result = $db->sql_query($sql)) )
{
message_die(GENERAL_ERROR, "Could not obtain post/user information.", '', __LINE__, __FILE__, $sql);
}
Dadurch werden anstatt eine Abfrage zwar zwei gemacht, aber im Ergebnis gewinne ich im Schnitt 3 Sekunden (!!!).
Erst fragt er die ID's ab per:
Code: Alles auswählen
viewtopic.php
Zeile: 785
DB: 0.0003s
Anfrage
SELECT p.post_id FROM phpbb_posts p USE INDEX(topic_n_id) WHERE p.topic_id = 6311 ORDER BY p.post_id DESC LIMIT 1
Tabelle Typ Mögliche Schlüssel Benutzter Schlüssel Schlüssel Länge Ref. Zeilen Kommentar
p
und dann holt er sich die Daten:
Code: Alles auswählen
viewtopic.php
Zeile: 801
DB: 0.0004s
Anfrage
SELECT pt.post_sub_title, u.username, u.user_id, u.user_posts, u.user_from, u.user_firstname, u.user_ebayname, u.user_car, 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_gender, p.*, pt.post_text, pt.post_subject, pt.bbcode_uid FROM phpbb_posts p, phpbb_users u, phpbb_posts_text pt WHERE p.post_id in (176663) AND pt.post_id = p.post_id AND u.user_id = p.poster_id ORDER BY p.post_time
Tabelle Typ Mögliche Schlüssel Benutzter Schlüssel Schlüssel Länge Ref. Zeilen Kommentar
p const PRIMARY,poster_id PRIMARY 3 const 1
u const PRIMARY PRIMARY 3 const 1
pt
Man beachte die Zeiten
