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 (IMG:style_emoticons/default/winksmiley.jpg)

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?

[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