Witam,
W skrypcie php mam funkcję, która zawiera "ciężkie" zapytanie do bazy, po wyłączeniu ów "dodatku" strona ładuje się bardzo szybko.. Po włączeniu logowania slow queries na serwerze oczywiście owe zapytanie jest tam logowane... (IMG:
http://forum.php.pl/style_emoticons/default/worriedsmiley.gif)
Funkcja wraz z zapytaniami:
<?php
function get_beers()
{
global $forum_id, $topic_id, $post_id, $userdata, $board_config, $db;
if ( $this->config['topic_poster_only'] && $topic_id )
{
$sql = " select topic_first_post_id from " . TOPICS_TABLE . " where topic_id = $topic_id";
if ( !($results = $db->sql_query($sql)) )
{
message_die(GENERAL_ERROR, 'Couldn't obtain topic first post id.', '', __LINE__, __FILE__, $sql);
}
$_data = $db->sql_fetchrow($results);
$this->topic_first_post_id = intval($_data['topic_first_post_id']); }
$user_id = $userdata['user_id'];
if ( $topic_id > 0 )
{
$sql = " select p1.post_id as post_id_sec, b.*, u1.username as beer_poster, u2.user
_id, u2.user_beers_src, u2.user_beers_dst, u1.user_level, u1.user_jr,
p2.poster_id as poster_id_sec, t.topic_id as topic_id_sec, f.forum_id as forum_i
d_sec
from " . POSTS_TABLE . " p1
left join " . BEER_TABLE . " b ON b.post_id = p1.post_id
left join " . POSTS_TABLE . " p2 ON p2.post_id = b.post_id
left join " . TOPICS_TABLE . " t ON t.topic_id = b.topic_id
left join " . FORUMS_TABLE . " f ON f.forum_id = b.forum_id
left join " . USERS_TABLE . " u1 ON u1.user_id = b.beer_src
left join " . USERS_TABLE . " u2 ON u2.user_id = p1.poster_id
where p1.topic_id = $topic_id " . (($user_id > 1) ? " or b.beer_src = $user_id or b.beer_dst = $user_id" : '');
}
else if ( $topic_id < 1 && $post_id > 0 )
{
$sql = " select p1.post_id as post_id_sec, b.*, u1.username as beer_poster, u2.user
_id, u2.user_beers_src, u2.user_beers_dst, u1.user_level, u1.user_jr,
p2.poster_id as poster_id_sec, t.topic_id as topic_id_sec, f.forum_id as forum_i
d_sec
from " . POSTS_TABLE . " p0
left join " . POSTS_TABLE . " p1 ON p1.topic_id = p0.topic_id
left join " . BEER_TABLE . " b ON (b.post_id = p1.post_id )
left join " . POSTS_TABLE . " p2 ON p2.post_id = b.post_id
left join " . TOPICS_TABLE . " t ON t.topic_id = b.topic_id
left join " . FORUMS_TABLE . " f ON f.forum_id = b.forum_id
left join " . USERS_TABLE . " u1 ON u1.user_id = b.beer_src
left join " . USERS_TABLE . " u2 ON u2.user_id = p1.poster_id
where p0.post_id = $post_id ";
}
else
{
return false;
}
if ( !($results = $db->sql_query($sql)) )
{
message_die(GENERAL_ERROR, 'Couldn't obtain topic beers', '', __LINE__, __FILE__, $sql);
}
$this->topic_replies = $trash_beers = array(); while ( $row = $db->sql_fetchrow($results) )
{
if ( $row['beer_id'] )
{
if ( $row['beer_dst'] == $row['beer_src'] || $row['poster_id_sec'] == $row['beer_src'] || !$row['poster_id_sec'] || !$row['forum_id_sec'] || !$row['topic_id_sec'] || !$row['beer_poster'] )
{
$trash_beers[] = $row['beer_id'];
}
else
{
if ( $row['beer_src'] == $user_id && $user_id > 1 ) $this->user_src++;
if ( $row['beer_dst'] == $user_id && $user_id > 1 ) $this->user_dst++;
$this->posts[$row['post_id']][] = array( 'user_id' =>$row['beer_src'], 'username' =>$row['beer_poster'], 'user_level' =>$row['user_level'], 'user_jr' =>$row['user_jr'] ); $this->post_users[$row['post_id']][] = $row['beer_src'];
if ( @!in_array($row[$row['post_id']]['beer_src'], $this->topic_replies) ) {
$this->topic_replies[$row['post_id']][] = $row['beer_src'];
}
}
}
$this->users[$row['user_id']]['src'] = intval($row['user_beers_src']); $this->users[$row['user_id']]['dst'] = intval($row['user_beers_dst']); }
if ( count($trash_beers) ) {
$sql = "delete from " . BEER_TABLE
. " where beer_id IN (" . implode(',', $trash_beers) . ")"; if ( !$db->sql_query($sql) )
{
message_die(GENERAL_ERROR, 'Couldn't delete fake topic beers', '', __LINE__, __FILE__, $sql);
}
}
if ( $userdata['user_beers_src'] != $this->user_src )
$u_sql[] = " user_beers_src = " . intval($this->user_src) . " "; if ( $userdata['user_beers_dst'] != $this->user_dst )
$u_sql[] = " user_beers_dst = " . intval($this->user_dst) . " "; {
$sql = "update " . USERS_TABLE
. " set " . implode(',', $u_sql) . " where user_id = $user_id"; if ( !$db->sql_query($sql) )
{
message_die(GENERAL_ERROR, 'Couldn't update user topic beers info', '', __LINE__, __FILE__, $sql);
}
$userdata['user_beers_src'] = intval($this->user_src); $userdata['user_beers_dst'] = intval($this->user_dst); }
}
?>
Chodzi o te zapytania z Left Join, da się coś z nimi zrobić? Może jakoś zastąpić Left Join innym?
Pozdrawiam.