Powolne przeglądanie produktów Opcje

[Walian]

Witam,

Pracuję nad pewnym serwisem internetowym (niestety nie wolno mi podawać nazwy ani adresu), który zaczyna już dość mocno zwalniać. W chwili obecnej problem stanowią dwie tabele:
- tabela produktów - MyISAM, ponad 700 tyś rekordów, ponad 180 MB.
- tabela ofert do produktów - MyISAM, ponad 1 125 000 rekordów, ponad 230 MB.

Do rzeczy - problem stanowi już samo przeglądanie produktów.

[SQL] pobierz, plaintext 
SELECT 
	SQL_NO_CACHE 
	SQL_CALC_FOUND_ROWS 
	`p`.`produkt_id`,
	`p`.`name`,
	`p`.`katalog`,
	`p`.`pod_katalog`,
	`p`.`category_id`,
	`o`.`user_id`,
	`o`.`cena`,
	`o`.`id` AS `id_offer` 
FROM 
	`produkty` `p` 
INNER JOIN 
	`oferty` `o` 
ON 
	`o`.`produkt_id`=`p`.`produkt_id` 
LEFT JOIN 
	`uzytkownicy` `u` 
ON 
	`u`.`user_id`=`o`.`user_id` 
WHERE 
		`u`.`aktywny`=1 ; Tu waskie gardlo...
		; W chwili obecnej wszyscy maja "1", ale TO sie kiedys zmieni...
	AND 
		`p`.`category_id` IN (tutaj 176 numerow (cale drzewko) kategorii) 
	AND ; Od tego momentu rozne filtry:
		`o`.`cena` BETWEEN 1 AND 99999 
ORDER BY 
	`p`.`name` ASC ; Zamiast tego moze sie pojawic sortowanie wg ceny
LIMIT 0,20
[SQL] pobierz, plaintext 

20 rows in set (9.70 sec)

Bez SQL_CALC_FOUND_ROWS:

20 rows in set (6.63 sec)

To samo zapytanie, ale bez wyciągania danych - zamiast tego COUNT(*):

20 rows in set (6.71 sec)

Teraz wracając do początku:
Bez "`u`.`aktywny`=1":

20 rows in set (1.86 sec)

Bez "`u`.`aktywny`=1" oraz bez "ORDER BY `p`.`name` ASC":

20 rows in set (1.16 sec)

Bez "`u`.`aktywny`=1" oraz bez "`o`.`cena` BETWEEN 1 AND 99999" oraz bez "ORDER BY `p`.`name` ASC":

20 rows in set (1.07 sec)

Ostatnie zapytanie, ale bez SQL_CALC_FOUND_ROWS:

20 rows in set (0.00 sec)

Serwis stoi na serwerze dedykowanym...

Jak przyspieszyć to zapytanie? Nie mogę zrezygnować z tych warunków WHERE, ale może da radę jakoś inaczej zapytanie skonstruować?
Przejście na InnoDB pomoże czy rozwiązania trzeba szukać gdzie indziej?

[erix]

A nie możesz po prostu wywołać dwóch zapytań? Jedno count z tymi warunkami, drugie właściwe? Myślę, że szybciej będzie.

[nospor]

zrob z tego zapytania EXPLAIN i zobacz na czym muli.
Zapewne musisz pozakładać własciwe indexy

[Walian]

A nie możesz po prostu wywołać dwóch zapytań? Jedno count z tymi warunkami, drugie właściwe? Myślę, że szybciej będzie.

To samo zapytanie, ale bez wyciągania danych - zamiast tego COUNT(*):

20 rows in set (6.71 sec)

Jak widzisz jest jeszcze gorzej, już bardziej się opłaca z SQL_CALC_FOUND_ROWS.

EXPLAIN EXTENDED:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE u ALL PRIMARY NULL NULL NULL 892 Using where; Using temporary; Using filesort
1 SIMPLE o ref produkt_id,user_id,cena user_id 3 u.user_id 19 Using where
1 SIMPLE a eq_ref PRIMARY,category_id PRIMARY 4 o.produkt_id 1 Using where

Próbowałem już wcześniej z różnymi indeksami, z pozbywaniem się filesort-a, nawet z FORCE INDEX, no ale nie wiem jak sobie z tym poradzić.

[erix]

Huh, nie zauważyłem...

Pokaż lepiej

[SQL] pobierz, plaintext 
SHOW CREATE TABLE
[SQL] pobierz, plaintext 

A jeśli chodzi o rozwiązanie - musisz każdorazowo odpytywać DB? Nie myślałeś o cache po stronie klienta? (nie mylić z cache demona)

[Walian]

Ale jak niby taki cache zrobić? Nie widzę tu sensu, skoro najmniejsza zmiana w filtrze, przejście do innej kategorii, czy wyszukiwanie wg jakieś frazy zmienia już postać i ilość wyników. Po stronie serwera też nie ma sensu bo powstanie olbrzymia ilość plików cache. Jeśli do tego uwzględnić fakt, że ciągle są dodawane lub aktualizowane produkty to liczba "kombinacji" jest olbrzymia.

[SQL] pobierz, plaintext 
SHOW CREATE TABLE `uzytkownicy`;
CREATE TABLE `uzytkownicy` (
 `user_id` int(11) NOT NULL AUTO_INCREMENT,
 `aktywny` int(11) NOT NULL DEFAULT '1',
 PRIMARY KEY  (`user_id`),
 FULLTEXT KEY `nazwa_sklepu` (`nazwa_sklepu`),
 FULLTEXT KEY `city` (`city`)
) ENGINE=MyISAM AUTO_INCREMENT=100962 DEFAULT CHARSET=latin2 PACK_KEYS=0
-- Indeks na polu "aktywny" nic nie daje, bo w kazdym rekordzie jest "1", przynajmniej na razie
 
SHOW CREATE TABLE `produkty`;
CREATE TABLE `produkty` (
 `produkt_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
 `name` varchar(255) NOT NULL,
 `category_id` smallint(11) NOT NULL DEFAULT '0',
 `katalog` tinyint(4) NOT NULL DEFAULT '0',
 `pod_katalog` tinyint(4) NOT NULL DEFAULT '0',
 PRIMARY KEY  (`produkt_id`),
 KEY `products_name` (`active`,`name`),
 KEY `category_id` (`category_id`),
 FULLTEXT KEY `name` (`name`)
) ENGINE=MyISAM AUTO_INCREMENT=911612 DEFAULT CHARSET=utf8 PACK_KEYS=0
 
SHOW CREATE TABLE `oferty`;
CREATE TABLE `oferty` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `user_id` mediumint(10) NOT NULL DEFAULT '0',
 `cena` double(16,2) NOT NULL DEFAULT '0.00',
 `nazwa_prod` varchar(255) NOT NULL,
 `offer_id` bigint(24) UNSIGNED NOT NULL,
 `produkt_id` int(10) UNSIGNED NOT NULL,
 PRIMARY KEY  (`id`),
 KEY `produkt_id` (`produkt_id`),
 KEY `user_id` (`user_id`),
 KEY `cena` (`cena`)
) ENGINE=MyISAM AUTO_INCREMENT=1357689 DEFAULT CHARSET=utf8
[SQL] pobierz, plaintext 

Wyciąłem kolumny, które nie biorą udziału w zapytaniu. Zresztą jest ich od cholery...
Wiem, wiem - tragedia... ale muszę to jakoś przyspieszyć ;/

[erix]

Wyciąłem kolumny, które nie biorą udziału w zapytaniu. Zresztą jest ich od cholery...

Hmm, a nie myślałeś o rozbiciu tego na wiele tabel? Np. opisy w osobnej, łączone relacją 1-1.

Jeśli są kolumny typu text/varchar - przyspieszy to wyszukiwanie.

[wookieb]

Daj całe create table (nie usuwaj "zbędnych" kolumn) bo nie mam jak tego przetestować na bazie aby w troszkę mniejszym stopniu pobawić się z optymalizacją.

Jeśli są kolumny typu text/varchar - przyspieszy to wyszukiwanie.

Nie ma to żadnego znaczenia w tym zapytaniu

[Walian]

Gdybym projektował ten serwis od początku to tak właśnie bym zrobił bo upraszcza to również eksport danej tabeli, czy jej przywracanie.
W sumie najwięcej kolumn (39, większość to VARCHAR) jest w tabeli userów, tabele produktów i ofert akurat nie mają ich dużo.
Niestety musiałbym napisać skrypt, który to wszystko rozdzieli, a potem zatrzymać serwis na czas wszystkich operacji. A nie chcę robić tego wszystkiego jeśli nie mam gwarancji, że to da cokolwiek. Tym bardziej, że dopiero przenieśliśmy się na dedyka, co zajęło masę czasu.

Myślisz, że na pewno się opłaci? W sumie i tak musiałbym poczekać z tym do poniedziałku, aż wróci drugi programista. Do tego czasu szukam różnych, alternatywnych rozwiązań.

EDIT: To wyżej było do erixa, spóźniłem się z postem
wookieb - zaraz zapodam.

Ten post edytował Walian 17.08.2010, 12:09:15

[kaminskp]

Sugeruję zastosowanie AJAX on szybko dłubie w danych i zwraca wyniki.

[erix]

Nie ma to żadnego znaczenia w tym zapytaniu

Czy ja wiem? Jeśli są kolumny różnej długości, to nie ma możliwości odczytu sekwencyjnego (paczkami o stałej długości), tylko trzeba każdorazowo tę długość sprawdzać i wtedy skakać...

~kaminskp - tiaa, tylko że w poruszanym problemie nie ma jak wykonać jakiegokolwiek JS...

[Walian]

[SQL] pobierz, plaintext 
CREATE TABLE `produkty` (
 `produkt_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
 `name` varchar(255) NOT NULL,
 `category_id` smallint(11) NOT NULL DEFAULT '0',
 `active` tinyint(4) NOT NULL DEFAULT '0',
 `poprawiony` tinyint(4) NOT NULL DEFAULT '0',
 `katalog` tinyint(4) NOT NULL DEFAULT '0',
 `pod_katalog` tinyint(4) NOT NULL DEFAULT '0',
 PRIMARY KEY  (`produkt_id`),
 KEY `products_name` (`active`,`name`),
 KEY `category_id` (`category_id`),
 FULLTEXT KEY `name` (`name`)
) ENGINE=MyISAM AUTO_INCREMENT=913300 DEFAULT CHARSET=utf8 PACK_KEYS=0
[SQL] pobierz, plaintext 

[SQL] pobierz, plaintext 
CREATE TABLE `oferty` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `user_id` mediumint(10) NOT NULL DEFAULT '0',
 `cena` double(16,2) NOT NULL DEFAULT '0.00',
 `nazwa_prod` varchar(255) NOT NULL,
 `link` varchar(255) NOT NULL,
 `offer_id` bigint(24) UNSIGNED NOT NULL,
 `produkt_id` int(10) UNSIGNED NOT NULL,
 `aktualizacja` int(8) NOT NULL,
 PRIMARY KEY  (`id`),
 KEY `produkt_id` (`produkt_id`),
 KEY `user_id` (`user_id`),
 KEY `cena` (`cena`)
) ENGINE=MyISAM AUTO_INCREMENT=1359539 DEFAULT CHARSET=utf8
[SQL] pobierz, plaintext 

A teraz czas na MATRIX:

[SQL] pobierz, plaintext 
CREATE TABLE `uzytkownicy` (
 `user_id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(100) character SET utf8 NOT NULL,
 `email` varchar(255) character SET utf8 NOT NULL,
 `birthdate` date NOT NULL DEFAULT '0000-00-00',
 `address` varchar(100) character SET utf8 NOT NULL,
 `city` varchar(50) character SET utf8 collate utf8_polish_ci NOT NULL,
 `state` varchar(100) character SET utf8 NOT NULL,
 `country` varchar(100) character SET utf8 NOT NULL,
 `zip_code` varchar(30) NOT NULL DEFAULT '',
 `phone` varchar(30) NOT NULL DEFAULT '',
 `username` varchar(255) NOT NULL DEFAULT '',
 `password` varchar(32) NOT NULL DEFAULT '',
 `active` tinyint(4) NOT NULL DEFAULT '0',
 `zdjecie_logo` varchar(255) NOT NULL,
 `xml_link` varchar(225) NOT NULL,
 `xml_nazwa` varchar(50) NOT NULL,
 `pp` int(3) NOT NULL DEFAULT '0',
 `opis_problem` varchar(255) character SET utf8 NOT NULL,
 `payment_status` varchar(20) NOT NULL DEFAULT '',
 `automat` int(11) NOT NULL DEFAULT '0',
 `poprawny_plik` int(3) NOT NULL DEFAULT '0',
 `newsletter` tinyint(4) NOT NULL DEFAULT '0',
 `reg_date` int(11) NOT NULL DEFAULT '0',
 `mail_activated` tinyint(4) NOT NULL DEFAULT '0',
 `lang` varchar(255) NOT NULL DEFAULT 'english',
 `preferred_seller` tinyint(4) NOT NULL DEFAULT '0',
 `promowanie_sklepu` tinyint(4) NOT NULL DEFAULT '0',
 `birthdate_year` int(11) NOT NULL DEFAULT '0',
 `referred_by` varchar(200) NOT NULL DEFAULT '',
 `payment_mode` tinyint(1) DEFAULT '0',
 `ilosc_prod` int(11) DEFAULT '0',
 `nazwa_sklepu` varchar(100) character SET utf8 DEFAULT NULL,
 `tax_account_type` tinyint(4) NOT NULL DEFAULT '0',
 `salt` char(3) NOT NULL DEFAULT '',
 `approved` tinyint(4) NOT NULL DEFAULT '0',
 `aktualizacja` text NOT NULL,
 `prom_logo` int(3) NOT NULL DEFAULT '0',
 `akceptacja_promowanie` int(2) NOT NULL,
 `aktywny` int(11) NOT NULL DEFAULT '1',
 PRIMARY KEY  (`user_id`),
 KEY `shop_active` (`active`,`user_id`),
 KEY `stores_list` (`active`),
 KEY `acc_overdue_users` (`payment_mode`,`reg_date`),
 KEY `active_users` (`active`,`reg_date`,`approved`),
 KEY `users_tax_acc_type` (`tax_account_type`,`reg_date`),
 KEY `users_tax_exempt` (`promowanie_sklepu`,`reg_date`),
 KEY `active` (`active`),
 FULLTEXT KEY `nazwa_sklepu` (`nazwa_sklepu`),
 FULLTEXT KEY `city` (`city`)
) ENGINE=MyISAM AUTO_INCREMENT=100962 DEFAULT CHARSET=latin2 PACK_KEYS=0
[SQL] pobierz, plaintext 

Pewnie kilka kolumn nie jest w ogóle używanych, to się je prędzej czy później wywali.

[wookieb]

Sugeruję zastosowanie AJAX on szybko dłubie w danych i zwraca wyniki.

Idź do kąta, zastanów się nad swoim postępowaniem i nie gadaj głupot.

[erix]

Na próbę - przenieś kolumny varchar/text (tyle, ile się da) do osobnej tabeli, np. userMeta zostawiając pozostałe. Oczywiście nazwa użytkownika i login trzeba zostawić.

I teraz zobacz, ile czasu zajmie wyciągnięcie z tej pierwszej, z atrybutem aktywny=1. Podejrzewam, że przyspieszy. Wtedy JOIN z drugą tabelą (relacja 1-1 po np. ID użytkownika) i pokaż, jak wychodzi czas.

Nie siedzę aż tak mocno w bazach, więc moje myślenie może być błędne (niech kto poprawi, jeśli tak jest), ale patrząc na logikę działania wydaje mi się całkiem sensowne.

Najlepiej niech zweryfikują to fakty.

[wookieb]

Niestety MyIsam słabo spisuje się w operacjach złączenia (wynika to między innymi z tego powodu, że nie przechowuje głównego indeksu tabeli w kluczach tak jak INNODB).
Po próbie zmiany myisam na innodb złączenia odrazu zaczęły wykorzystywać klucze.
Próbuję "conieco" zoptymalizować tabele i wynikami pochwale się jak tylko uda mi się coś zrobić aczkolwiek zastanów się na przyszłość czy nie lepiej przejśc na INNODB i do wyszukiwania wykorzystać sphinxa bądź inne systemy wyszukiwania (w zastępstwie FULLTEXT).

Ten post edytował wookieb 17.08.2010, 12:40:00

[Walian]

Stworzyłem nawet nieco mniejszą tabelkę dla testu:

[SQL] pobierz, plaintext 
CREATE TABLE `uzytkownicy2` (
 `user_id` int(11) NOT NULL AUTO_INCREMENT,
 `state` varchar(100) character SET utf8 NOT NULL,
 `nazwa_sklepu` varchar(100) character SET utf8 DEFAULT NULL,
 `aktywny` int(11) NOT NULL DEFAULT '1',
 PRIMARY KEY  (`user_id`),
 FULLTEXT KEY `nazwa_sklepu` (`nazwa_sklepu`)
) ENGINE=MyISAM AUTO_INCREMENT=100962 DEFAULT CHARSET=latin2 PACK_KEYS=0
[SQL] pobierz, plaintext 

Niestety ZERO przyspieszenia. A nawet działa nieco wolniej, choć to może być przypadek.

wookieb - ja zawsze byłem za InnoDB, ale jak wspominałem - nie ja zacząłem projektować ten serwis. Co do Sphinxa - nigdy z niego nie korzystałem, tak więc nie znam się na nim, wyczytałem jedynie mnóstwo pozytywnych opinii na jego temat, także kiedyś na pewno się nim pobawię, ale chyba nie w tym serwisie, bo raczej nie będzie na to czasu.

Ten post edytował Walian 17.08.2010, 12:45:27

[dr_bonzo]

Hmm, indeksy na kolumnach zlaczen są.

Robisz LEFT JOINA do userów, a potem WHERE user.is_active = 1.
Czyli wlasciwie LEFT JOIN ci nie potrzebny - bo jesli nie znajdize usera to kolumny 'userowe' beda mialy wszedzie NULLe a NULL =?= true zawsze jest NIEspelnione.

Po tej zmianie EXPLAIN dla tabeli userow uzywa u mnie klucza glownego zamiast robic full scan'a.
-----
Przed

Kod

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE u ALL PRIMARY NULL NULL NULL 892 Using where; Using temporary; Using filesort
1 SIMPLE o ref produkt_id,user_id,cena user_id 3 u.user_id 19 Using where
1 SIMPLE a eq_ref PRIMARY,category_id PRIMARY 4 o.produkt_id 1 Using where

// produkty ma alias 'a' ?

Kod

id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra    
1    SIMPLE    u    index    PRIMARY    PRIMARY    4    NULL    12    Using index; Using temporary; Using filesort
1    SIMPLE    o    ref    produkt_id,user_id,cena    user_id    3    test.u.user_id    15    Using where
1    SIMPLE    p    eq_ref    PRIMARY,category_id    PRIMARY    4    test.o.produkt_id    1    Using where

jako że danych mam neiwiele (180 ofert) to czasów nie jestem w stanie podać.

Ten post edytował dr_bonzo 17.08.2010, 13:13:15

[Walian]

Nie kumam - jeśli nie LEFT JOIN to co?

bo jesli nie znajdize usera to kolumny 'userowe' beda mialy wszedzie NULLe a NULL =?= true zawsze jest NIEspelnione.

Sprawdziłem tak:

[SQL] pobierz, plaintext 
SELECT SQL_NO_CACHE COUNT(*) FROM
(
SELECT SQL_NO_CACHE `p`.`produkt_id`,`p`.`name`,`p`.`katalog`,`p`.`pod_katalog`,`p`.`category_id`,`o`.`user_id`,`o`.`cena`,`o`.`id` AS id_offer,`u`.`aktywny` FROM `produkty` `p` INNER JOIN `oferty` `o` ON `o`.`auction_id`=`p`.`auction_id` LEFT JOIN `uzytkownicy` `u` ON `u`.`user_id`=`o`.`user_id` WHERE `u`.`aktywny`=1 AND `p`.`category_id` IN (numerki) AND `o`.`cena` BETWEEN 1 AND 99999 ORDER BY `p`.`name` ASC LIMIT 0,20
) AS `r`
WHERE
aktywny = NULL;
[SQL] pobierz, plaintext 

Zwróciło 0.
Chyba, że źle zrozumiałem lub źle sprawdziłem.

Nie wiem, co żeś za zapytanie zmajstrował, że Ci takie EXPLAIN wyszło.

[VegetaSSJ]

a wiedziałeś o tym że:

[SQL] pobierz, plaintext 
`p`.`category_id` IN (tutaj 176 numerow (cale drzewko) kategorii) 
[SQL] pobierz, plaintext 

rozbijane jest na:

[SQL] pobierz, plaintext 
 AND (`p`.`category_id` = nr1 OR `p`.`category_id` = nr2 OR `p`.`category_id` = nr3 OR ...  `p`.`category_id` = nr176)
[SQL] pobierz, plaintext 

jeżeli masz drzewko kategorii oparte na left-right lepiej będzie zastąpić to beetwenem z podaniem odpwiednich wartści roota drzewka.

Ten post edytował VegetaSSJ 17.08.2010, 14:33:07

[dr_bonzo]

Nie kumam - jeśli nie LEFT JOIN to co?

To INNER JOIN (zapomnialem dopisac)

i

...WHERE
aktywny = NULL;

o czyms zapomniales:

[SQL] pobierz, plaintext 
SELECT NULL = NULL, 1 = NULL, 0 = NULL, 0 = 1, 0 = 0
[SQL] pobierz, plaintext 

---
no i SQLka szukajaca

[SQL] pobierz, plaintext 
SELECT 
	SQL_NO_CACHE  	SQL_CALC_FOUND_ROWS  	`p`.`produkt_id`, 	`p`.`name`, 	`p`.`katalog`, 	`p`.`pod_katalog`, 	`p`.`category_id`, 	`o`.`user_id`, 	`o`.`cena`,	`o`.`id` AS `id_offer`, u.user_id  
FROM  `produkty` `p` 
INNER JOIN  `oferty` `o`  ON `o`.`produkt_id`=`p`.`produkt_id` 
INNER JOIN `uzytkownicy` `u`  ON `u`.`user_id`=`o`.`user_id` 
WHERE 
--	`u`.`aktywny`=1  AND 
	`p`.`category_id` IN (2, 23, 32, 35, 48, 49, 52, 65, 66, 77, 79, 89, 98) AND -- 180 IDekow kategorii
	`o`.`cena` BETWEEN 1 AND 99999 
ORDER BY 
	`p`.`name` ASC
[SQL] pobierz, plaintext 

LIMIT 0,20

Ten post edytował dr_bonzo 17.08.2010, 14:39:16