[MySQL] "Ciężkie" zapytanie - optymalizacja? Opcje

[@Wu]

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...



Funkcja wraz z zapytaniami:

[PHP] pobierz, plaintext 
<?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); 
} 
} 
$u_sql = array(); 
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) . " "; 
if ( count($u_sql) ) 
{ 
$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); 
} 
}
?>
[PHP] pobierz, plaintext 

Chodzi o te zapytania z Left Join, da się coś z nimi zrobić? Może jakoś zastąpić Left Join innym? 



Pozdrawiam.

[Zbychu666]

1) Mogłeś dać info że to mod do forum phpBB.
2) Możesz pomęczyć autora moda żeby zoptymalizował zwoje "dzieło".
3) Wstaw do tego kodu echo $sql; i zapytanie wrzuć do phpMyAdmina dodają na początku EXPLAIN. Wklej na forum wynik, najlepiej w postaci screena, to będzie nad czym dyskutować.

[@Wu]

1) No fakt, jest to mod, ale głównie chodzi o jedno konkretne zapytanie...
2) Niestety "nie wspiera" już modyfikacji...
3) Screen:

[Zbychu666]

Problem jest w tym kawałku zapytania, które robi pełen skan tabelki z postami.

Kod

where p1.topic_id = $topic_id \" . (($user_id > 1) ? \" or b.beer_src = $user_id or b.beer_dst = $user_id\" : '');

Możnaby o spróbować zmienić w ten sposób, bo zgaduje że to zapytanie ma podliczać tylko dla danego topicu:

Kod

where p1.topic_id = $topic_id \" . (($user_id > 1) ? \" AND (b.beer_src = $user_id OR b.beer_dst = $user_id)\" : '');

Ten post edytował Zbychu666 2.08.2008, 21:09:22

[@Wu]

Niestety, po takim zabiegu liczba piw (beer) wynosi 0... Czyli źle liczy ilość.

[kwiateusz]

te explainy i indexy to mi srednio wychodza ale moze zaloz index na topic_id w posts bo mi tak z explaina wychodzi ze tego brak

[osiris]

Indeks na kolumnie topic_id jest juz zalozony (jak widac w kolumnie possible keys) ale nie zostaje uzyty z powodu warunku WHERE p1.topic_id = 34872 OR b.beer_src = 2 OR b.beer_dst = 2

Chodzi o to ze uzycie indeksu w tym przypadku nie jest mozliwe.
Sprobuj moze czegos takiego:

[PHP] pobierz, plaintext 
<?php
if ( $topic_id > 0 ) 
{ 
$sql = " select p1.post_id as post_id_sec, b.*, u1.username as beer_poster, u2.user
_id, 
2.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
_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 ";
  if  (($user_id > 1) {
	 $sql = "( $sql ) UNION (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
_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 b.beer_src = $user_id or b.beer_dst = $user_id)"; 
  } 
}
?>
[PHP] pobierz, plaintext 

co do drugiego warunku:

[PHP] pobierz, plaintext 
<?php
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
?>
[PHP] pobierz, plaintext 

po co tutaj wogole uzywasz tabeli p0? zmien jej aliasa na p1 i wywal pierwsze zlaczenie i zmien alias w klauzuli where

Ten post edytował osiris 2.08.2008, 23:00:25

[@Wu]

Działa, tylko...
Strona wygenerowana w 7.58 sekund. Zapytań do SQL: 14

Niestety to nic nie pomogło

[osiris]

wklej explainy z nowych zapytan.

moze to problemem nie jest baza danych a skrypt php?

[@Wu]

Zapytanie na screenie nie jest widoczne w całości..(obcięte).

Jak widać, teraz 2 takie zapytania się robią.

Ten post edytował @Wu 3.08.2008, 10:06:55

[osiris]

sprobuj zalozyc indeksy na tabeli BEER_TABLE:
1) dwie kolumny: pierwsza post_id, druga beer_src
2) dwie kolumny: pierwsza post_id, druga beer_dst

potem wykonaj jeszcze raz te zapytania i wrzuc explainy

Ten post edytował osiris 3.08.2008, 11:21:01

[@Wu]

Jak je założyć? Jakieś zapytania?

[osiris]

Widze ze korzystasz z phpmyadmin, wiec:
- przejdz do widoku struktury tabeli BEER_TABLE
- wybierz opcje: twórz indeks dla x kolumn (wpisz 2)
- w nastepnym ekranie wybierz te pola ktore Ci podalem (kolejnosc sie liczy) i zatwierdz

[@Wu]

A tutaj w przypadku "oryginalnego" zapytania:



Niestety ani indexy, ani zmienione zapytanie nic nie dało

[dr_bonzo]

Mozesz wkleic SQL tworzacy tabele [wszystkie tu wymienione] + SQLki ktorych uzywac to bedziemy moigli sie nimi pobawic

[@Wu]

Podać kod zapytania SQL, które tworzy tabele beers?

[PHP] pobierz, plaintext 
<?php
CREATE TABLE IF NOT EXISTS `phpbb_beers` (
  `beer_id` int(10) unsigned NOT NULL auto_increment,
  `beer_src` int(10) NOT NULL default '0',
  `beer_dst` int(10) NOT NULL default '0',
  `post_id` int(10) NOT NULL default '0',
  `topic_id` int(12) NOT NULL default '0',
  `forum_id` int(12) NOT NULL default '0',
  `beer_time` int(12) NOT NULL default '0',
  PRIMARY KEY  (`beer_id`),
  KEY `post_id` (`post_id`),
  KEY `topic_id` (`topic_id`),
  KEY `forum_id` (`forum_id`)
) TYPE=MyISAM  AUTO_INCREMENT=466293 ;
?>
[PHP] pobierz, plaintext 

Tak mi przyszło do głowy...Może te zapytanie wykorzystuje jakąś funkcję dla której jest za mało przydzielonej pamięci (konfiguracja MySQL)?

[kwiateusz]

jedna rzecz mi przychodzi na mysl wykonaj zapytanie w phpmyadminie i zobacz ile czasu samo zapytanie zajmuje, moze ono jest jeszcze w miare, a czas przetwarzania zawala kod php?

a co do postu dr_bonzo chodiło o create wszystkich tabel uzytych w zapytaniu

[osiris]

Hmm, ok zacznijmy od innej strony. Jakie dane ma wyciagac ta funkcja? ilosc piw dla kazdej wiadomosci w watku? ilosc piw dla kazdego uzytkownika piszacego w danym watku?

[@Wu]

Wydaje mi się, że liczba piw, gdyż nawet w widoku profilu też jest używane.

W końcu to.. "function get_beers()".



kwiateusz, w przypadku gdy dodał się Limit 0 , 30   zapytanie było w 0,0002 sekundy chyba, w przypadku gdy zmieniłem na Limit 0 , 20000 - połączenie zostało przerwane... (limit czasu upłynął?)

Ten post edytował @Wu 3.08.2008, 19:15:42