Pomoc - Szukaj - Użytkownicy - Kalendarz
Pełna wersja: Optymalizacja zapytania
Forum PHP.pl > Forum > Bazy danych > MySQL
kilas88
9.03.2009, 18:10:05
Witam.

Mam w bazie takie oto tabele z autorami i wierszami :

love_authors
Kod
ID, NAME_FIRST, NAME_LAST, NAME_PREFIX, NAME_URL, MAIL, DESCRIPTION, IMAGE, STATUS


love_lyrics
Kod
ID, AUTHOR_ID, TITLE


Chcę na stronie teraz wyświetlić autorów wraz z liczbą wierszy (liryka zliczana z tabeli love_lyrics). Relacja zachodzi pomiędzy ID i AUTHOR_ID. Dotychczas robiłem to w następujący sposób:

[SQL] pobierz, plaintext 
  1. SELECT
  2.   ID,
  3.   CONCAT(NAME_LAST, ' ', NAME_FIRST) AS AUTHOR_NAME, 
  4.   NAME_PREFIX,
  5.   NAME_URL,
  6.   DESCRIPTION,
  7.   IMAGE
  8.  
  9. FROM   love_authors
  10.   
  11. WHERE   STATUS = 1
  12.   
  13. ORDER BY   AUTHOR_NAME, NAME_PREFIX
  14.  
  15. LIMIT 0, 5
[SQL] pobierz, plaintext


Oraz w pętli while dla każdego rekordu pobieram osobno liczbę wierszy:
[SQL] pobierz, plaintext 
  1. SELECT count(ID) FROM love_lyrics WHERE STATUS = 1 AND AUTHOR_ID=".$get_author['ID']."
[SQL] pobierz, plaintext


Dla każdej podstrony wykonywanych jest więc kilka połączeń (6). Czy mógłbym jakoś połączyć te tabele w celu optymalizacji zapytania?

Z góry dziękuję za wszelkie podpowiedzi.
lukasamd
9.03.2009, 18:17:01
Hm... może dodaj do love_authors pole liczbowe LYRICS w nim trzymaj bieżącą liczbę wierszy.
W wypadku dodawania / usuwania wiersza, wykonasz zapytanie które doda lub odejmie od tej wartości 1.

W ten sposób nie tylko wywalisz konieczność robienia 6x COUNT, ale umożliwisz łatwe przedstawianie tego, kto napisał najwięcej np.

[SQL] pobierz, plaintext 
  1. SELECT CONCAT(NAME_LAST, ' ', NAME_FIRST) AS AUTHOR_NAME, LYRICS FROM love_authors WHERE STATUS = 1 ORDER BY LYRICS DESC
[SQL] pobierz, plaintext
kilas88
9.03.2009, 18:20:18
Wiem wiem, myślałem nad tym. Tyle tylko tym sposobem dodając lub usuwając wiersze musiałbym aktualizować 2 tabele, więc zawsze trochę więcej roboty smile.gif jednak to byłoby chyba najbardziej optymalnym rozwiązaniem, jak sądze?
wookieb
9.03.2009, 18:20:44
Można i byłoby to raczej najszybsze rozwiazanie.
Lecz można to tez policzyc w jednym zapytaniu
[SQL] pobierz, plaintext 
  1. SELECT
  2.  a.ID,
  3.   CONCAT(a.NAME_LAST, ' ', a.NAME_FIRST) AS AUTHOR_NAME, 
  4.   a.NAME_PREFIX,
  5.   a.NAME_URL,
  6.   a.DESCRIPTION,
  7.   a.IMAGE,
  8. (SELECT count(ID) FROM love_lyrics WHERE STATUS = 1 AND AUTHOR_ID=a.ID) AS ile
  9.  
  10. FROM love_authors a
  11.  
  12. WHERE a.STATUS = 1
  13.  
  14. ORDER BY a.AUTHOR_NAME, a.NAME_PREFIX
  15.  
  16. LIMIT 0, 5
[SQL] pobierz, plaintext
kilas88
9.03.2009, 21:13:11
Dziękuję za pomoc, skorzystam z podzapytań smile.gif być może z czasem przerzucę się na dodatkową kolumnę, zobaczymy co życie pokaże biggrin.gif




mam jeszcze jedno pytanie odnośnie optymalizacji zapytań na ww. bazy danych. mam więc następujące tabele w bazie:

love_authors
ID, NAME_FIRST, NAME_LAST, NAME_PREFIX, NAME_URL, MAIL, DESCRIPTION, IMAGE, STATUS

love_lyrics
ID, AUTHOR_ID, TITLE

love_tags
ID, ID_PAGE, TAG_URL, TAG_VAL

mam jakiś wiersz na stronie. chciałbym, by z powyższych tabel zostały pobrane podobne wiersze, np. 5 tekstów tego samego autora i 5 tekstów z tych samych tagów.

dotychczas robię dwa połączenia - pobieram 5 tagów i 5 tekstów tego samego autora, po czym łącze wszystko w jedną tablicę i obcinam z najbardziej trafnymi wynikami:

[PHP] pobierz, plaintext 
  1. <?php
  2. // zdefiniowanie zmiennej przechowującej all materiały
  3. $all_related_stuff = array();
  4.   
  5. if ($author_id) {
  6.  
  7.   // pobranie rekordów tego samego autora
  8.   $get_lyrics_cnt = mysql_query(&#092;"
  9.   SELECT 
  10.     wiersz.ID AS WIERSZ_ID,
  11.     wiersz.TITLE AS WIERSZ_TYTUL,
  12.     wiersz.LINK_TITLE AS WIERSZ_URL,
  13.     wiersz.LANGUAGE AS WIERSZ_LANG,
  14.     CONCAT(autor.NAME_FIRST, ' ', autor.NAME_LAST) AS AUTHOR_NAME,
  15.     autor.NAME_PREFIX AS AUTHOR_NICK
  16.    
  17.   FROM
  18.     ".get_array('tables', 'wiersze')." AS wiersz,
  19.     ".get_array('tables', 'autorzy')." AS autor
  20.  
  21.   WHERE
  22.     wiersz.STATUS = 1
  23.     AND wiersz.ID <> '$current_id'
  24.     AND wiersz.AUTHOR_ID = '$author_id'
  25.     AND wiersz.AUTHOR_ID = autor.ID
  26.  
  27.   ORDER BY RAND()
  28.   LIMIT 10");
  29.  
  30.   if (@mysql_num_rows($get_lyrics_cnt) > 0)
  31.     while ($get_text = mysql_fetch_assoc($get_lyrics_cnt)) {
  32.       $AUTHOR_NAME = !empty($get_text['AUTHOR_NAME']) ? $get_text['AUTHOR_NAME'] : $get_text['AUTHOR_NICK'];
  33.       $all_related_stuff[$get_text['WIERSZ_URL']] = array($get_text['WIERSZ_ID'], $get_text['WIERSZ_TYTUL'], $get_text['WIERSZ_URL'], catch_data($AUTHOR_NAME, false), $get_text['WIERSZ_LANG']);
  34.     }
  35.  
  36.     // pobranie rekordów o tych samych tagach
  37.     $get_tags_cnt = mysql_query(\"
  38.     SELECT 
  39.       wiersz.ID AS WIERSZ_ID,
  40.       wiersz.TITLE AS WIERSZ_TYTUL,
  41.       wiersz.LINK_TITLE AS WIERSZ_URL,
  42.       wiersz.LANGUAGE AS WIERSZ_LANG,
  43.       CONCAT(autor.NAME_FIRST, ' ', autor.NAME_LAST) AS AUTHOR_NAME,
  44.       autor.NAME_PREFIX AS AUTHOR_NICK
  45.    
  46.     FROM
  47.       ".get_array('tables', 'wiersze')." AS wiersz,
  48.       ".get_array('tables', 'tagi')." AS tag,
  49.       ".get_array('tables', 'autorzy')." AS autor
  50.  
  51.     WHERE
  52.       wiersz.STATUS = 1
  53.       AND tag.CAT_ID = '$cat_ide'
  54.       AND wiersz.TAGS_ID = tag.ID_PAGE
  55.       AND wiersz.ID <> '$current_id'
  56.       AND wiersz.AUTHOR_ID = autor.ID
  57.  
  58.     GROUP BY wiersz.TAGS_ID
  59.  
  60.     ORDER BY RAND()
  61.     LIMIT 10");
  62.  
  63.   if (@mysql_num_rows($get_tags_cnt) > 0)
  64.     while ($get_text = mysql_fetch_assoc($get_tags_cnt)) {
  65.       $AUTHOR_NAME = !empty($get_text['AUTHOR_NAME']) ? $get_text['AUTHOR_NAME'] : $get_text['AUTHOR_NICK'];
  66.       $all_related_stuff[$get_text['WIERSZ_URL']] = array($get_text['WIERSZ_ID'], $get_text['WIERSZ_TYTUL'], $get_text['WIERSZ_URL'], catch_data($AUTHOR_NAME, false), $get_text['WIERSZ_LANG']);
  67.     }
  68.  
  69.     // jeśli istnieją podobych materiały, wyświetlenie ich na stronie
  70.     if (count($all_related_stuff) > 0) {
  71.  
  72.       $related_template = array();
  73.       $related_template['item_list'] = null;
  74.    
  75.        // obcięcie liczby przechowywanych linków do 10
  76.       array_slice ($all_related_stuff, 0, 10);
  77.  
  78.       foreach ($all_related_stuff as $related_stuff => $related_item) {
  79.    
  80.         $set_link = array();
  81.         $set_link['link_url'] = create_link('milosny_wiersz', array('id' => $related_item[0], 'n' => $related_stuff));
  82.         $set_link['link_text'] = catch_data($related_item[3], false) .' - '. catch_data($related_item[1], false);
  83.         $set_link['link_lang'] = $related_item[4];
  84.   
  85.         $set_link['cat_url'] = create_link ('milosne_wiersze');
  86.         $set_link['cat_txt'] = 'Miłosna liryka i wiersze';
  87.   
  88.         $related_template['item_list'] .= template('related_links_item', $set_link, 1);
  89.         unset($set_link);
  90.     }
  91.    
  92.   return template('related_links', $related_template, 1);
  93.   }
  94. }
  95. ?>
[PHP] pobierz, plaintext



Czy mógłbym wykonać to jakoś bardziej przemyślanie? Jestem w trakcie czytania świetnej książki o MySQL (Paul DoBois 'MySQL'), lecz dopiero zaczynam tę lekturę i słaby jestem tongue.gif
griken
11.03.2009, 00:39:39
Cytat(wookieb @ 9.03.2009, 19:20:44 ) *
Można i byłoby to raczej najszybsze rozwiazanie.
Lecz można to tez policzyc w jednym zapytaniu
[SQL] pobierz, plaintext 
  1. SELECT
  2.  a.ID,
  3.   CONCAT(a.NAME_LAST, ' ', a.NAME_FIRST) AS AUTHOR_NAME, 
  4.   a.NAME_PREFIX,
  5.   a.NAME_URL,
  6.   a.DESCRIPTION,
  7.   a.IMAGE,
  8. (SELECT count(ID) FROM love_lyrics WHERE STATUS = 1 AND AUTHOR_ID=a.ID) AS ile
  9.  
  10. FROM love_authors a
  11.  
  12. WHERE a.STATUS = 1
  13.  
  14. ORDER BY a.AUTHOR_NAME, a.NAME_PREFIX
  15.  
  16. LIMIT 0, 5
[SQL] pobierz, plaintext


wookieb mam male pytanko (moze sie orientujesz), a nie lepiej wyrzucic 'STATUS = 1' z podzapytania jesli podany warunek jest juz sprecyzowany w glownym WHERE? tj.

[SQL] pobierz, plaintext 
  1. SELECT a.ID,CONCAT(a.NAME_LAST, ' ', a.NAME_FIRST) AS AUTHOR_NAME,
  2. (SELECT count(ID) FROM love_lyrics WHERE AUTHOR_ID=a.ID) AS ile
  3. FROM love_authors AS a
  4. WHERE a.STATUS = 1
  5. ORDER BY AUTHOR_NAME
  6. LIMIT 0, 5
[SQL] pobierz, plaintext


albo nawet

[SQL] pobierz, plaintext 
  1. SELECT a.ID,CONCAT(a.NAME_LAST, ' ', a.NAME_FIRST) AS AUTHOR_NAME,
  2. count(love_lyrics.`id`) AS ile
  3. FROM love_authors AS a
  4. LEFT JOIN love_lyrics ON a.`id`=love_lyrics.`AUTHOR_ID`
  5. WHERE a.STATUS = 1
  6. GROUP BY a.id
  7. ORDER BY AUTHOR_NAME
  8. LIMIT 0, 5
[SQL] pobierz, plaintext


jak myslisz, ktore bedzie wydajniesze w tym wypadku? sprawdz explaina dla obydwoch, dla drugiego zapytania pokazuje zawsze 'select_type' -> 'SIMPLE' ale jednoczesnie 'Using where; Using temporary; Using filesort' co nie jest mile widzane, prawda?
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.