Pomoc - Szukaj - Użytkownicy - Kalendarz
Pełna wersja: [MySQL] "Ciężkie" zapytanie - optymalizacja?
Forum PHP.pl > Forum > Bazy danych > MySQL
@Wu
2.08.2008, 09:23:25
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... worriedsmiley.gif



Funkcja wraz z zapytaniami:



[PHP] pobierz, plaintext 
  1. <?php
  2. function get_beers() 
  3. { 
  4. global $forum_id, $topic_id, $post_id, $userdata, $board_config, $db; 
  5.  
  6. if ( $this->config['topic_poster_only'] && $topic_id ) 
  7. { 
  8. $sql = " select topic_first_post_id from " . TOPICS_TABLE . " where topic_id = $topic_id"; 
  9. if ( !($results = $db->sql_query($sql)) ) 
  10. { 
  11. message_die(GENERAL_ERROR, 'Couldn't obtain topic first post id.', '', __LINE__, __FILE__, $sql); 
  12. } 
  13. $_data = $db->sql_fetchrow($results); 
  14. $this->topic_first_post_id = intval($_data['topic_first_post_id']); 
  15. } 
  16.  
  17. $user_id = $userdata['user_id']; 
  18. if ( $topic_id > 0 ) 
  19. { 
  20. $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, 
  21. p2.poster_id as poster_id_sec, t.topic_id as topic_id_sec, f.forum_id as forum_i
    d_sec 
  22. from " . POSTS_TABLE . " p1 
  23. left join " . BEER_TABLE . " b ON b.post_id = p1.post_id 
  24. left join " . POSTS_TABLE . " p2 ON p2.post_id = b.post_id 
  25. left join " . TOPICS_TABLE . " t ON t.topic_id = b.topic_id 
  26. left join " . FORUMS_TABLE . " f ON f.forum_id = b.forum_id 
  27. left join " . USERS_TABLE . " u1 ON u1.user_id = b.beer_src 
  28. left join " . USERS_TABLE . " u2 ON u2.user_id = p1.poster_id 
  29. where p1.topic_id = $topic_id " . (($user_id > 1) ? " or b.beer_src = $user_id or b.beer_dst = $user_id" : ''); 
  30. } 
  31. else if ( $topic_id < 1 && $post_id > 0 ) 
  32. { 
  33. $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, 
  34. p2.poster_id as poster_id_sec, t.topic_id as topic_id_sec, f.forum_id as forum_i
    d_sec 
  35. from " . POSTS_TABLE . " p0 
  36. left join " . POSTS_TABLE . " p1 ON p1.topic_id = p0.topic_id 
  37. left join " . BEER_TABLE . " b ON (b.post_id = p1.post_id ) 
  38. left join " . POSTS_TABLE . " p2 ON p2.post_id = b.post_id 
  39. left join " . TOPICS_TABLE . " t ON t.topic_id = b.topic_id 
  40. left join " . FORUMS_TABLE . " f ON f.forum_id = b.forum_id 
  41. left join " . USERS_TABLE . " u1 ON u1.user_id = b.beer_src 
  42. left join " . USERS_TABLE . " u2 ON u2.user_id = p1.poster_id 
  43. where p0.post_id = $post_id "; 
  44. } 
  45. else 
  46. { 
  47. return false; 
  48. } 
  49. if ( !($results = $db->sql_query($sql)) ) 
  50. { 
  51. message_die(GENERAL_ERROR, 'Couldn't obtain topic beers', '', __LINE__, __FILE__, $sql); 
  52. } 
  53.  
  54. $this->topic_replies = $trash_beers = array(); 
  55. while ( $row = $db->sql_fetchrow($results) ) 
  56. { 
  57. if ( $row['beer_id'] ) 
  58. { 
  59. 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'] ) 
  60. { 
  61. $trash_beers[] = $row['beer_id']; 
  62. } 
  63. else 
  64. { 
  65. if ( $row['beer_src'] == $user_id && $user_id > 1 ) $this->user_src++; 
  66. if ( $row['beer_dst'] == $user_id && $user_id > 1 ) $this->user_dst++; 
  67. $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'] ); 
  68. $this->post_users[$row['post_id']][] = $row['beer_src']; 
  69. if ( @!in_array($row[$row['post_id']]['beer_src'], $this->topic_replies) ) 
  70. { 
  71. $this->topic_replies[$row['post_id']][] = $row['beer_src']; 
  72. } 
  73. } 
  74. } 
  75. $this->users[$row['user_id']]['src'] = intval($row['user_beers_src']); 
  76. $this->users[$row['user_id']]['dst'] = intval($row['user_beers_dst']); 
  77. } 
  78. if ( count($trash_beers) ) 
  79. { 
  80. $sql = "delete from " . BEER_TABLE . " where beer_id IN (" . implode(',', $trash_beers) . ")"; 
  81. if ( !$db->sql_query($sql) ) 
  82. { 
  83. message_die(GENERAL_ERROR, 'Couldn't delete fake topic beers', '', __LINE__, __FILE__, $sql); 
  84. } 
  85. } 
  86. $u_sql = array(); 
  87. if ( $userdata['user_beers_src'] != $this->user_src ) 
  88. $u_sql[] = " user_beers_src = " . intval($this->user_src) . " "; 
  89. if ( $userdata['user_beers_dst'] != $this->user_dst ) 
  90. $u_sql[] = " user_beers_dst = " . intval($this->user_dst) . " "; 
  91. if ( count($u_sql) ) 
  92. { 
  93. $sql = "update " . USERS_TABLE . " set " . implode(',', $u_sql) . " where user_id = $user_id"; 
  94. if ( !$db->sql_query($sql) ) 
  95. { 
  96. message_die(GENERAL_ERROR, 'Couldn't update user topic beers info', '', __LINE__, __FILE__, $sql); 
  97. } 
  98. $userdata['user_beers_src'] = intval($this->user_src); 
  99. $userdata['user_beers_dst'] = intval($this->user_dst); 
  100. } 
  101. }
  102. ?>
[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
2.08.2008, 11:20:18
1) Mogłeś dać info że to mod do forum phpBB.
2) Możesz pomęczyć autora moda żeby zoptymalizował zwoje "dzieło". tongue.gif
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
2.08.2008, 12:23:20
1) No fakt, jest to mod, ale głównie chodzi o jedno konkretne zapytanie...
2) Niestety "nie wspiera" już modyfikacji...
3) Screen:
Zbychu666
2.08.2008, 21:08:44
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)\" : '');
@Wu
2.08.2008, 21:55:40
Niestety, po takim zabiegu liczba piw (beer) wynosi 0... Czyli źle liczy ilość.
kwiateusz
2.08.2008, 22:24:18
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 smile.gif
osiris
2.08.2008, 22:59:48
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 
  1. <?php
  2. if ( $topic_id > 0 ) 
  3. { 
  4. $sql = " select p1.post_id as post_id_sec, b.*, u1.username as beer_poster, u2.user
    _id, 
  5. 2.user_beers_src, u2.user_beers_dst, u1.user_level, u1.user_jr, 
  6. p2.poster_id as poster_id_sec, t.topic_id as topic_id_sec, f.forum_id as forum_i
  7. _sec 
  8. from " . POSTS_TABLE . " p1 
  9. left join " . BEER_TABLE . " b ON b.post_id = p1.post_id 
  10. left join " . POSTS_TABLE . " p2 ON p2.post_id = b.post_id 
  11. left join " . TOPICS_TABLE . " t ON t.topic_id = b.topic_id 
  12. left join " . FORUMS_TABLE . " f ON f.forum_id = b.forum_id 
  13. left join " . USERS_TABLE . " u1 ON u1.user_id = b.beer_src 
  14. left join " . USERS_TABLE . " u2 ON u2.user_id = p1.poster_id 
  15. where p1.topic_id = $topic_id ";
  16.   if  (($user_id > 1) {
  17. 	 $sql = "( $sql ) UNION (select p1.post_id as post_id_sec, b.*, u1.username as beer_poster, u2.user
  18. id, u2.user_beers_src, u2.user_beers_dst, u1.user_level, u1.user_jr, 
  19. p2.poster_id as poster_id_sec, t.topic_id as topic_id_sec, f.forum_id as forum_i
  20. _sec 
  21. from " . POSTS_TABLE . " p1 
  22. left join " . BEER_TABLE . " b ON b.post_id = p1.post_id 
  23. left join " . POSTS_TABLE . " p2 ON p2.post_id = b.post_id 
  24. left join " . TOPICS_TABLE . " t ON t.topic_id = b.topic_id 
  25. left join " . FORUMS_TABLE . " f ON f.forum_id = b.forum_id 
  26. left join " . USERS_TABLE . " u1 ON u1.user_id = b.beer_src 
  27. left join " . USERS_TABLE . " u2 ON u2.user_id = p1.poster_id 
  28. where b.beer_src = $user_id or b.beer_dst = $user_id)"; 
  29.   } 
  30. }
  31. ?>
[PHP] pobierz, plaintext


co do drugiego warunku:
[PHP] pobierz, plaintext 
  1. <?php
  2. else if ( $topic_id < 1 && $post_id > 0 ) 
  3. { 
  4. $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, 
  5. p2.poster_id as poster_id_sec, t.topic_id as topic_id_sec, f.forum_id as forum_i
    d_sec 
  6. from " . POSTS_TABLE . " p0 
  7. left join " . POSTS_TABLE . " p1 ON p1.topic_id = p0.topic_id 
  8. left join " . BEER_TABLE . " b ON (b.post_id = p1.post_id ) 
  9. left join " . POSTS_TABLE . " p2 ON p2.post_id = b.post_id 
  10. left join " . TOPICS_TABLE . " t ON t.topic_id = b.topic_id 
  11. left join " . FORUMS_TABLE . " f ON f.forum_id = b.forum_id 
  12. left join " . USERS_TABLE . " u1 ON u1.user_id = b.beer_src 
  13. left join " . USERS_TABLE . " u2 ON u2.user_id = p1.poster_id 
  14. where p0.post_id = $post_id "; 
  15. } 
  16. else
  17. ?>
[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
@Wu
3.08.2008, 07:17:05
Działa, tylko...
Strona wygenerowana w 7.58 sekund. Zapytań do SQL: 14

Niestety to nic nie pomogło sad.gif
osiris
3.08.2008, 09:08:43
wklej explainy z nowych zapytan.

moze to problemem nie jest baza danych a skrypt php?
@Wu
3.08.2008, 10:04:59


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

Jak widać, teraz 2 takie zapytania się robią.
osiris
3.08.2008, 11:20:31
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
@Wu
3.08.2008, 11:32:34
Jak je założyć? Jakieś zapytania?
osiris
3.08.2008, 11:57:06
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
3.08.2008, 16:15:40


A tutaj w przypadku "oryginalnego" zapytania:



Niestety ani indexy, ani zmienione zapytanie nic nie dało sad.gif
dr_bonzo
3.08.2008, 16:23:27
Mozesz wkleic SQL tworzacy tabele [wszystkie tu wymienione] + SQLki ktorych uzywac to bedziemy moigli sie nimi pobawic
@Wu
3.08.2008, 16:27:58
Podać kod zapytania SQL, które tworzy tabele beers?

[PHP] pobierz, plaintext 
  1. <?php
  2. CREATE TABLE IF NOT EXISTS `phpbb_beers` (
  3.   `beer_id` int(10) unsigned NOT NULL auto_increment,
  4.   `beer_src` int(10) NOT NULL default '0',
  5.   `beer_dst` int(10) NOT NULL default '0',
  6.   `post_id` int(10) NOT NULL default '0',
  7.   `topic_id` int(12) NOT NULL default '0',
  8.   `forum_id` int(12) NOT NULL default '0',
  9.   `beer_time` int(12) NOT NULL default '0',
  10.   PRIMARY KEY  (`beer_id`),
  11.   KEY `post_id` (`post_id`),
  12.   KEY `topic_id` (`topic_id`),
  13.   KEY `forum_id` (`forum_id`)
  14. ) TYPE=MyISAM  AUTO_INCREMENT=466293 ;
  15. ?>
[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
3.08.2008, 16:31:27
jedna rzecz mi przychodzi na mysl smile.gif 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 smile.gif
osiris
3.08.2008, 18:00:21
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
3.08.2008, 18:58:19
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ął?)
To jest wersja lo-fi głównej zawartości. Aby zobaczyć pełną wersję z większą zawartością, obrazkami i formatowaniem proszę kliknij tutaj.
Invision Power Board © 2001-2025 Invision Power Services, Inc.