Optymalizacja zapytania Opcje

[kilas88]

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 
SELECT
  ID,
  CONCAT(NAME_LAST, ' ', NAME_FIRST) AS AUTHOR_NAME, 
  NAME_PREFIX,
  NAME_URL,
  DESCRIPTION,
  IMAGE
 
FROM   love_authors
  
WHERE   STATUS = 1
  
ORDER BY   AUTHOR_NAME, NAME_PREFIX
 
LIMIT 0, 5
[SQL] pobierz, plaintext 

Oraz w pętli while dla każdego rekordu pobieram osobno liczbę wierszy:

[SQL] pobierz, plaintext 
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.

Ten post edytował kilas88 9.03.2009, 18:11:44

[-lukasamd-]

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 
SELECT CONCAT(NAME_LAST, ' ', NAME_FIRST) AS AUTHOR_NAME, LYRICS FROM love_authors WHERE STATUS = 1 ORDER BY LYRICS DESC
[SQL] pobierz, plaintext 

[kilas88]

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 jednak to byłoby chyba najbardziej optymalnym rozwiązaniem, jak sądze?

[wookieb]

Można i byłoby to raczej najszybsze rozwiazanie.
Lecz można to tez policzyc w jednym zapytaniu

[SQL] pobierz, plaintext 
SELECT
 a.ID,
  CONCAT(a.NAME_LAST, ' ', a.NAME_FIRST) AS AUTHOR_NAME, 
  a.NAME_PREFIX,
  a.NAME_URL,
  a.DESCRIPTION,
  a.IMAGE,
(SELECT count(ID) FROM love_lyrics WHERE STATUS = 1 AND AUTHOR_ID=a.ID) AS ile
 
FROM love_authors a
 
WHERE a.STATUS = 1
 
ORDER BY a.AUTHOR_NAME, a.NAME_PREFIX
 
LIMIT 0, 5
[SQL] pobierz, plaintext 

Ten post edytował wookieb 9.03.2009, 18:21:05

[kilas88]

Dziękuję za pomoc, skorzystam z podzapytań być może z czasem przerzucę się na dodatkową kolumnę, zobaczymy co życie pokaże




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 
<?php
// zdefiniowanie zmiennej przechowującej all materiały
$all_related_stuff = array();
  
if ($author_id) {
 
  // pobranie rekordów tego samego autora
  $get_lyrics_cnt = mysql_query(&#092;"
  SELECT 
    wiersz.ID AS WIERSZ_ID,
    wiersz.TITLE AS WIERSZ_TYTUL,
    wiersz.LINK_TITLE AS WIERSZ_URL,
    wiersz.LANGUAGE AS WIERSZ_LANG,
    CONCAT(autor.NAME_FIRST, ' ', autor.NAME_LAST) AS AUTHOR_NAME,
    autor.NAME_PREFIX AS AUTHOR_NICK
   
  FROM
    ".get_array('tables', 'wiersze')." AS wiersz,
    ".get_array('tables', 'autorzy')." AS autor
 
  WHERE
    wiersz.STATUS = 1
    AND wiersz.ID <> '$current_id'
    AND wiersz.AUTHOR_ID = '$author_id'
    AND wiersz.AUTHOR_ID = autor.ID
 
  ORDER BY RAND()
  LIMIT 10");
 
  if (@mysql_num_rows($get_lyrics_cnt) > 0)
    while ($get_text = mysql_fetch_assoc($get_lyrics_cnt)) {
      $AUTHOR_NAME = !empty($get_text['AUTHOR_NAME']) ? $get_text['AUTHOR_NAME'] : $get_text['AUTHOR_NICK'];
      $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']);
    }
 
    // pobranie rekordów o tych samych tagach
    $get_tags_cnt = mysql_query(\"
    SELECT 
      wiersz.ID AS WIERSZ_ID,
      wiersz.TITLE AS WIERSZ_TYTUL,
      wiersz.LINK_TITLE AS WIERSZ_URL,
      wiersz.LANGUAGE AS WIERSZ_LANG,
      CONCAT(autor.NAME_FIRST, ' ', autor.NAME_LAST) AS AUTHOR_NAME,
      autor.NAME_PREFIX AS AUTHOR_NICK
   
    FROM
      ".get_array('tables', 'wiersze')." AS wiersz,
      ".get_array('tables', 'tagi')." AS tag,
      ".get_array('tables', 'autorzy')." AS autor
 
    WHERE
      wiersz.STATUS = 1
      AND tag.CAT_ID = '$cat_ide'
      AND wiersz.TAGS_ID = tag.ID_PAGE
      AND wiersz.ID <> '$current_id'
      AND wiersz.AUTHOR_ID = autor.ID
 
    GROUP BY wiersz.TAGS_ID
 
    ORDER BY RAND()
    LIMIT 10");
 
  if (@mysql_num_rows($get_tags_cnt) > 0)
    while ($get_text = mysql_fetch_assoc($get_tags_cnt)) {
      $AUTHOR_NAME = !empty($get_text['AUTHOR_NAME']) ? $get_text['AUTHOR_NAME'] : $get_text['AUTHOR_NICK'];
      $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']);
    }
 
    // jeśli istnieją podobych materiały, wyświetlenie ich na stronie
    if (count($all_related_stuff) > 0) {
 
      $related_template = array();
      $related_template['item_list'] = null;
   
       // obcięcie liczby przechowywanych linków do 10
      array_slice ($all_related_stuff, 0, 10);
 
      foreach ($all_related_stuff as $related_stuff => $related_item) {
   
        $set_link = array();
        $set_link['link_url'] = create_link('milosny_wiersz', array('id' => $related_item[0], 'n' => $related_stuff));
        $set_link['link_text'] = catch_data($related_item[3], false) .' - '. catch_data($related_item[1], false);
        $set_link['link_lang'] = $related_item[4];
  
        $set_link['cat_url'] = create_link ('milosne_wiersze');
        $set_link['cat_txt'] = 'Miłosna liryka i wiersze';
  
        $related_template['item_list'] .= template('related_links_item', $set_link, 1);
        unset($set_link);
    }
   
  return template('related_links', $related_template, 1);
  }
}
?>
[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

Ten post edytował kilas88 9.03.2009, 21:17:05

[griken]

Można i byłoby to raczej najszybsze rozwiazanie.
Lecz można to tez policzyc w jednym zapytaniu

[SQL] pobierz, plaintext 
SELECT
 a.ID,
  CONCAT(a.NAME_LAST, ' ', a.NAME_FIRST) AS AUTHOR_NAME, 
  a.NAME_PREFIX,
  a.NAME_URL,
  a.DESCRIPTION,
  a.IMAGE,
(SELECT count(ID) FROM love_lyrics WHERE STATUS = 1 AND AUTHOR_ID=a.ID) AS ile
 
FROM love_authors a
 
WHERE a.STATUS = 1
 
ORDER BY a.AUTHOR_NAME, a.NAME_PREFIX
 
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 
SELECT a.ID,CONCAT(a.NAME_LAST, ' ', a.NAME_FIRST) AS AUTHOR_NAME,
(SELECT count(ID) FROM love_lyrics WHERE AUTHOR_ID=a.ID) AS ile
FROM love_authors AS a
WHERE a.STATUS = 1
ORDER BY AUTHOR_NAME
LIMIT 0, 5
[SQL] pobierz, plaintext 

albo nawet

[SQL] pobierz, plaintext 
SELECT a.ID,CONCAT(a.NAME_LAST, ' ', a.NAME_FIRST) AS AUTHOR_NAME,
count(love_lyrics.`id`) AS ile
FROM love_authors AS a
LEFT JOIN love_lyrics ON a.`id`=love_lyrics.`AUTHOR_ID`
WHERE a.STATUS = 1
GROUP BY a.id
ORDER BY AUTHOR_NAME
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?