Forum PHP.pl > Optymalizacja zapytania + indeksy

Pomoc - Szukaj - Użytkownicy - Kalendarz

Pełna wersja: Optymalizacja zapytania + indeksy

Forum PHP.pl > Forum > Bazy danych > MySQL

Walian

20.02.2010, 14:58:21

Mam takie zapytanie:

[SQL] pobierz, plaintext 
   SELECT 
      SQL_CALC_FOUND_ROWS 
      a.article_id,
      a.article_status,
      a.article_views,
      a.article_date_added,
      a.article_date_modified,
      a.article_desc,
      a.article_adder,
      a.article_last_changer,
      a.article_name,
      (
         SELECT 
            m.member_level 
         FROM 
            members AS `m` 
         WHERE 
            LOWER(m.member_username)=LOWER(a.article_adder)
      ) AS `adder_level`,
      (
         SELECT 
            m.member_level 
         FROM 
            members AS `m` 
         WHERE 
            LOWER(m.member_username)=LOWER(a.article_last_changer)
      ) AS `last_changer_level` 
      FROM 
         articles AS `a` 
      WHERE 
         a.article_category_id=1 -- tu odpowiednie Id
      ORDER BY 
         a.article_date_added DESC 
      LIMIT 0,20;
[SQL] pobierz, plaintext

Służy do wylistowania / wyszukania artykułów dla danej kategorii.
Dodatkowo zwraca mi dodatkowe informacje o dodającym oraz o osobie, która jako ostatnia edytowała artykuł (edytować może autor lub administrator), m.in to czy dodający lub ostatni edytujący jest administratorem, czy też zwykłym użytkownikiem. Oczywiście zamiast tego zera przy limicie pojawia się odpowiedni offset

Dla testu dodałem ok. 1000 artykułów do bazy.
Niestety zapytanie zbyt długo się wykonuje, a i tak testuję to tylko na localhoście.
Czas więc zoptymalizować składnię zapytania o ile się da no i dodać indeksy.

Tabela artykułów:

[SQL] pobierz, plaintext 
CREATE TABLE `articles` (
  `article_id` mediumint(8) UNSIGNED NOT NULL AUTO_INCREMENT,
  `article_category_id` tinyint(3) UNSIGNED NOT NULL DEFAULT 0,
  `article_status` tinyint(3) UNSIGNED NOT NULL DEFAULT 0,
  `article_views` int(11) UNSIGNED NOT NULL DEFAULT 0,
  `article_date_added` datetime NOT NULL,
  `article_date_modified` datetime NOT NULL,
  `article_desc` tinytext COLLATE utf8_polish_ci NOT NULL DEFAULT '',
  `article_content` mediumtext COLLATE utf8_polish_ci NOT NULL DEFAULT '',
  `article_adder` varchar(12) COLLATE utf8_polish_ci NOT NULL DEFAULT '',
  `article_last_changer` varchar(12) COLLATE utf8_polish_ci NOT NULL DEFAULT '',
  `article_name` varchar(64) COLLATE utf8_polish_ci NOT NULL DEFAULT '',
  `article_safe_name` varchar(64) COLLATE utf8_polish_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`article_id`),
  UNIQUE KEY `article_name` (`article_name`),
  UNIQUE KEY `article_safe_name` (`article_safe_name`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;
[SQL] pobierz, plaintext

Pole member_username w tabeli members:

[SQL] pobierz, plaintext 
`member_username` varchar(12) COLLATE utf8_polish_ci NOT NULL DEFAULT '',
[SQL] pobierz, plaintext

I pytanie tak na marginesie - stosować słówko AS w zapytaniach czy lepiej skrócony zapis bez tego słowa kluczowego?

gothye

20.02.2010, 17:12:46

skoro wyszukujesz po

[SQL] pobierz, plaintext 
article_category_id
[SQL] pobierz, plaintext

to czemu tutaj klucza nie zastosowałeś ?

Mchl

20.02.2010, 17:17:23

Podzapytania w liście pól wykonują się dla każdego wiersza tabeli 'articles'. Pora zapoznać się ze złączeniami.

Kod

<pre>   SELECT
       SQL_CALC_FOUND_ROWS
       a.article_id,
       a.article_status,
       a.article_views,
       a.article_date_added,
       a.article_date_modified,
       a.article_desc,
       a.article_adder,
       a.article_last_changer,
       a.article_name,
       ad.member_level as `adder_level`,
       alc.member.level as `last_changer_level`
       FROM
          articles as `a`
       INNER JOIN
         members AS ad
       ON
         LOWER(ad.member_username)=LOWER(a.article_adder)
       INNER JOIN
         members AS alc
       ON
         LOWER(alc.member_username)=LOWER(a.article_last_changer)
       WHERE
          a.article_category_id=1 -- tu odpowiednie Id
       ORDER BY
          a.article_date_added DESC
      LIMIT 0,20;

Walian

20.02.2010, 22:27:40

Ok dzięki Mchl.
Złączenia już wykorzystywałem kilka razy, jednak w tym wypadku nie udawało mi się uzyskać pożądanych efektów więc tymczasowo zastosowałem podzapytania.
Co prawda musiałem zmienić w Twoim zapytaniu INNER JOIN-y na LEFT JOIN-y bo nie dostawałem wyników, ale teraz już jest OK.
Tak w ogóle to masz literówkę w:

Cytat

alc.member.level

Cytat("gothye")

skoro wyszukujesz po

[SQL] pobierz, plaintext 
article_category_id
[SQL] pobierz, plaintext

to czemu tutaj klucza nie zastosowałeś ?

Bo kategorii będzie bardzo niewiele, na początku ok. 15, maksymalnie myślę, że około 30.

Mam jeszcze jeden mały problem.
W menu mam wylistowane kategorie i chcę by obok każdej w nawiasie pokazało ile przypada widocznych artykułów dla danej kategorii.
O tym czy artykuł jest widoczny decyduje jego status z zakresu od 0 do 6.
Widoczne są tylko te ze statusem od 0 do 2 lub ze statusem 6.
Kategorie też mają swoje statusy, ale tylko od 0 do 3 i widoczne są wszystkie ze statusem mniejszym niż 3.

Tabela kategorii:

[SQL] pobierz, plaintext 
CREATE TABLE `articles_categories` (
  `category_id` tinyint(3) UNSIGNED NOT NULL AUTO_INCREMENT,
  `category_sort_order` tinyint(3) UNSIGNED NOT NULL DEFAULT 0,
  `category_status` tinyint(3) UNSIGNED NOT NULL DEFAULT 0,
  `category_visible_articles` mediumint(8) UNSIGNED NOT NULL DEFAULT 0,
  `category_date_added` datetime NOT NULL,
  `category_date_modified` datetime NOT NULL,
  `category_adder` varchar(12) COLLATE utf8_polish_ci NOT NULL DEFAULT '',
  `category_last_changer` varchar(12) COLLATE utf8_polish_ci NOT NULL DEFAULT '',
  `category_name` varchar(32) COLLATE utf8_polish_ci NOT NULL DEFAULT '',
  `category_safe_name` varchar(32) COLLATE utf8_polish_ci NOT NULL DEFAULT '',
  `category_title` varchar(255) COLLATE utf8_polish_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`category_id`),
  UNIQUE KEY `category_name` (`category_name`),
  UNIQUE KEY `category_safe_name` (`category_safe_name`),
  UNIQUE KEY `category_title` (`category_title`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;
[SQL] pobierz, plaintext

Na razie najlepsze zapytanie jakie udało mi się wymyślić ma taką postać:

[SQL] pobierz, plaintext 
SELECT 
   ac.category_id,
   ac.category_status,
   ac.category_name,
   ac.category_title,
   COUNT(a.article_id) AS `articles_count` 
   FROM 
      articles_categories AS `ac` 
   LEFT JOIN 
      articles AS `a` 
   ON 
      (a.article_category_id=ac.category_id) AND ((a.article_status < 3) OR (a.article_status = 6))
   WHERE 
      (ac.category_status < 3) 
   GROUP BY 
      ac.category_id;
[SQL] pobierz, plaintext

Dla ok. 1000 artykułów zapytanie wykonuje się średnio w 0.45 s, czyli chyba za długo.
Indeksów chyba nie ma sensu zakładać na pola id i status prawda?

Jak sami widzicie zapytanie mocno kombinowane :/
Zapytanie musi mi zwrócić również kategorie nieposiadające artykułów (0 w nawiasie).

Mchl

20.02.2010, 22:47:46

Cytat(Walian @ 20.02.2010, 22:27:40 )

Bo kategorii będzie bardzo niewiele, na początku ok. 15, maksymalnie myślę, że około 30.

No to co? Ważne po ile będzie artykułów w każdej kategorii.

Spróbuj tak:

Kod

SELECT
   ac.category_id,
   ac.category_status,
   ac.category_name,
   ac.category_title,
   a.articles_count
FROM
   articles_categories AS `ac`
LEFT JOIN (
   SELECT
      article_category_id
      COUNT(*) AS articles_count
   FROM
      articles AS `a`
   WHERE
      (a.article_status < 3) OR (a.article_status = 6)
   GROUP BY
      article_category_id
) AS a
ON
   (a.article_category_id=ac.category_id)
WHERE
   (ac.category_status < 3)

Walian

20.02.2010, 23:19:21

0.13 s

Dzięki wielkie.

Mchl

20.02.2010, 23:37:53

Możesz spróbować jeszcze z indeksem na:
ALTER TABLE articles_categories ADD INDEX category_id_category_status(category_id,category_status)
albo odwrotnie... nigdy nie jestem pewien w którą stronę MySQL sobie ułoży kolumny

Walian

21.02.2010, 17:01:57

Mam jeszcze jeden problem, to zapytanie które służy do pobrania artykułów danego użytkownika ("Moje artykuły"):

[SQL] pobierz, plaintext 
EXPLAIN 
SELECT 
    SQL_CALC_FOUND_ROWS 
    a.article_id,
    a.article_status,
    a.article_views,
    a.article_date_added,
    a.article_date_modified,
    a.article_desc,
    a.article_adder,
    a.article_last_changer,
    a.article_name,
    alc.member_level AS `last_changer_level`
FROM 
   articles AS `a` 
LEFT JOIN 
   members AS `alc` 
ON 
   alc.member_username=a.article_last_changer 
WHERE 
   a.article_adder='wojtas' 
ORDER BY 
   a.article_date_added DESC 
LIMIT 0,20;
[SQL] pobierz, plaintext

Wynik:

Tabela ma łącznie 2247 artykułów czyli o 2 więcej niż ma testowy użytkownik.
Zapytanie bez explain wykonuje się w ok. 0.36 s.
Jak widać explain zwrócił "rows" o wartości 2245 - czy tak powinno być skoro ja wybieram tylko 20 artykułów? Zapytanie jest dobrze ułożone?

Mchl

21.02.2010, 20:45:12

SQL_CALC_FOUND_ROWS wymusza wykonanie zapytania bez LIMIT (w uproszczeniu), po to aby policzyć właśnie wszystkie wiersze spełniające dany warunek.

Walian

21.02.2010, 20:52:41

A bez uproszczenia

?

1. Wykonanie zapytań z EXPLAIN zarówno z SQL_CALC_FOUND_ROWS jak i bez niego - zwraca ten sam wynik.
2. Wykonanie obu zapytań bez EXPLAIN skutkuje tym, że to bez SQL_CALC_FOUND_ROWS jest prawie dwukrotnie szybsze.

Mchl

21.02.2010, 23:57:52

Generalnie zaufał bymw tym wypadku ludziom z Percony:
http://www.mysqlperformanceblog.com/2007/0...alc_found_rows/

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.