Optymalizacja zapytania + indeksy Opcje

[Walian]

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]

skoro wyszukujesz po

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

to czemu tutaj klucza nie zastosowałeś ? 

[Mchl]

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;

Ten post edytował Mchl 20.02.2010, 17:18:26

[Walian]

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:

alc.member.level

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

Ten post edytował Walian 20.02.2010, 22:32:54

[Mchl]

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)

Ten post edytował Mchl 20.02.2010, 22:48:14

[Walian]

0.13 s
Dzięki wielkie.

[Mchl]

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]

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]

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]

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]

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