Witam.

Borykam się ostatnio z wydajnością zapytania, w którym występuje złączenie dwóch dużych tabel (pierwsza 2mln rekordów, druga 4mln rekordów).

Zapytanie podstawowo wygląda tak:

[SQL] pobierz, plaintext 
SELECT * , p.`product_id` AS `product_id` , COUNT( DISTINCT (o.`store_id` )  ) AS `liczba_produktow` , MIN( o.`product_offer_price` ) AS `cena` 
FROM `products` p INNER JOIN `products_offers` o ON ( o.`product_id` = p.`product_id` AND o.`product_offer_status` = '1' ) 
WHERE p.`product_navi_3` = '1158'
GROUP BY p.`product_id` 
ORDER BY `liczba_produktow` DESC 
LIMIT 0 , 100
[SQL] pobierz, plaintext 

Na wszelkie pola biorące udział w warunkach zapytania (czyli product_id, store_id, product_offer_status, product_navi_3) są nałożone indeksy (indeksu nie ma jedynie na kolumnie ceny - product_offer_price). Ale nie to jest problemem. Z przeprowadzonych testów, spowolnienie (czy raczej zasobożerność) zwiększa klauzula ORDER BY.
Gdy klauzuli ORDER BY nie ma (tj. sortowanie nie odbywa się po kolumnie `liczba_produktow` lub `cena`) mySQL przegląda jedynie rekordy, które spełniają warunek WHERE (czyli product_navi_3 = 1158), w momencie gdy ORDER BY jest - mysql przegląda wszystkie rekordy w tabeli (2mln!).
Dzieje się tak dlatego, że nie ma indeksów na sortowanych kolumnach - ale nie ma (chyba?) możliwości nałożenia indeksu na kolumny, które są obliczane w "locie".

Dlatego też wykombinowałem SELECT z SELECT'a, który wygląda mniej więcej tak:

[SQL] pobierz, plaintext 
SELECT * 
FROM (
SELECT p.`product_name` , p.`product_id` , COUNT( DISTINCT (o.`store_id` ) ) AS `liczba_produktow` , MIN( o.`product_offer_price` ) AS `cena` 
FROM `products` p INNER JOIN `products_offers` o ON ( o.`product_id` = p.`product_id` AND o.`product_offer_status` = '1' AND p.`product_navi_1` = '1154' )
GROUP BY p.`product_id` 
LIMIT 0 , 5000
)res
ORDER BY res.`liczba_produktow` DESC 
LIMIT 0 , 100
[SQL] pobierz, plaintext 

Zapytanie zagnieżdzone pobiera tylko tyle rekordów ile jest w limicie (tj. nie przeszukuje całej tabeli). Wyszedłem z założenia, że mogę pobrać wszystkie rekordy i dopiero je posortować - w danym indeksie (kategorii zazwyczaj nie ma więcej niż 5000 rekordów), więc stosunkowo małe porcje danych są pobierane.
Niestety w zapytaniu dalej, dodanie ORDER BY powoduje, że mimo wszystko pobierane (przeglądane) są wszystkie dane z tabeli.

Czy jest jakieś rozwiązanie, aby mySQL na siłę nie próbował pobierać wszystkich danych ponownie, tylko wykonał sortowanie na tym mniejszym już zbiorze (z zagnieżdzonego selecta)?

Z góry dzięki za pomoc...

Na jakiej podstawie wnosisz, że przeglądane są wszystkie rekordy? Możesz wkleić wynik explaina?

Na podstawie EXPLAINA.

Zapytanie pierwsze (podstawowe):

[SQL] pobierz, plaintext 
id 	select_type 	TABLE 	type 	possible_keys 	KEY 	key_len 	ref 	rows 	Extra 
1	SIMPLE	p	INDEX	PRIMARY,product_navi_3	PRIMARY	4	NULL	1909102	USING WHERE; USING TEMPORARY; USING filesort
1	SIMPLE	o	ref	product_id,product_offer_status,product_store_id	product_id	4	admin_smartbay.p.product_id	2	USING WHERE
[SQL] pobierz, plaintext 

Zapytanie drugie (zagnieżdżone selecty):

[SQL] pobierz, plaintext 
d 	select_type 	TABLE 	type 	possible_keys 	KEY 	key_len 	ref 	rows 	Extra 
1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	5000	USING filesort
2	DERIVED	p	INDEX	PRIMARY,product_navi_1	product_navi_1	5	 	1909102	USING WHERE; USING filesort
2	DERIVED	o	ref	product_id,product_offer_status,product_store_id	product_id	4	p.product_id	2	USING WHERE
[SQL] pobierz, plaintext 

LIMIT wykonywany jest na samym końcu zapytania, więc wrzucenie go do podzapytania nie wpłynie na poprawę wydajności, bo tak czy tak wszystkie rekordy muszą najpierw zostać dopasowane, aby później można je było ograniczyć (podzapytanie musi dopasować wszystkie rekordy i dopiero wtedy wybiera pierwsze 5000). Ważniejsze jest jednak to, że zapytanie z podzapytaniem zadziała inaczej, aniżeli to pierwsze, bo w pierwszym sortujesz wszystkie rekordy a później ograniczasz ich liczbę, podczas gdy w drugim sortujesz ograniczoną liczbę rekordów. Zastanów się, co będzie, gdy wiersz 5001 będzie zawierał produkty, których w danej kategorii/sklepie jest więcej, aniżeli w wierszu 5000.

Po drugie wyrzuć ten DISTINCT z COUNT-a. DISTINCT to taki specyficzny sposób grupowania (tutaj sprawę powinno załatwić GROUP BY `p`.`product_id`), który przeważnie powoduje utworzenie tabeli tymczasowej.

Usunięcie DISTINCT nie wpłynie na to, że będzie pobieranych mniej rekordów z bazy - a jednocześnie informuje mnie ile jest produktów z RÓŻNYCH sklepów.
Tak samo pomijam, fakt, że może się zdarzyć, że 5001 produkt będzie tym najliczniejszym.

Zależy mi na tym, aby pobrać dane nie przeglądając całej tabeli. Nie wiem czemu mysql - mimo, że ma zawęzić sie tylko do produktów z danej kategorii - przegląda wszystkie rekordy.

1. Wywal * z zapytania. Na pewno nie pomaga
2. Skoro masz INNER JOINa, to spróbuj je odwrotnie połączyć. Z warunkiem p.`product_navi_3` = '1158' w JOIN, a nie we where. We wshere będziesz miał ten z drugiej tabeli.
3. sortuj po numerze kolumny. Np ORDER BY 4 DESC.
4. Zapomnij oi zapytaniu z podzapytania. Zabija MySQL-a.

Może to pomoże.

Dzięki.
Ad. 1. * - w celu uproszczenia zapytania
Ad. 2. Zmiana zależności (tj. select z offers i połaczenie z products) nie zmienia nic, to samo dodanie w warunku złączenia klauzuli product_navi_3 = 1158
Ad. 3. Nie zmienia to nic.

Głównym (w zasadzie jedynym) problemem jest fakt, że przy dowolnym zapytaniu, gdy chcę posortować dane wg. kolumny nie indeksowanej (czyli tej z COUNT) mySQL odczytuje całą tabelę, a nie tylko te dane, które są wybrane (czyli poprzez warunek WHERE).

Samo zapytanie wykonuje się dość szybko - tj. w ciągu 0.5sekundy. Jednakże wymaga to sporych operacji dyskowych i utworzenia tabel tymczasowych o wielkości 600MB. Co przy dużym stresie, słabo wpływa na wydajność.

Zastanawiałem się nad rozwiązaniem tego problemu - mam wrażenie, że to jakiś BUG mySQL'a i przeglądanie wszystkich danych w tym zapytaniu jest nie logiczne.
Dostępne są dwa rozwiązania - tyle, że bardziej skomplikowane - tj.:
1. Pobranie danych z podzapytania i wygenerowanie tabeli tymczasowej - dopiero z niej pobranie danych i posortowanie.
2. Pobranie danych z podzapytania i posortowanie w języku programowania.

Ale mam, wrażenie, że to rozwiązania mało eleganckie i trochę łopatologiczne.

Ile jest produktów spełniających ten warunek product_navi_3 = 1158? Najwyraźniej mimo istnienia indeksu i wypisania jako możliwy do użycia optimizer nie zdecydował się go użyć. Jedną z przyczyn może być to, że jeśli tych produktów jest bardzo dużo, to pełny skan tabeli może być szybszy niż szukanie przez indeks. Dzieje się tak dlatego, że sekwencyjne czytanie z dysku jest o rząd wielkości szybsze niż losowe, a w zapytaniu z tej tabeli potrzebujesz więcej niż tylko product_id i product_navi_3, co sprawia że ten indeks nie jest "covering" (jaki jest polski odpowiednik?), czyli nie zawiera wszystkich kolumn, które trzeba byłoby przeczytać. Więc i tak jeśli ten indeks miałby być użyty to zamiast przeczytać wszystkie wartości bezpośrednio z indeksu, trzeba by losowo skakać po tabeli by dostać brakujące w indeksie kolumny. Jeśli tabela jest w InnoDb, to indeks na product_navi_3 zawiera również product_id, więc ograniczenie się do tych dwóch kolumn w select powinno pomóc. Dla tabel MyISAM potrzebny byłby podwójny indeks (product_navi_3, product_id).
Oczywiście można też spróbować użyć FORCE INDEX( product_navi_3 ) po nazwie tabeli products i zobaczyć rezultaty czy faktycznie byłoby dzięki temu szybciej.
ANALYZE TABLE dla obu tabel też mogłoby pomóc jeśli nie było niedawno robione, dla odświeżenia statystyk indeksów. Jeżeli nie są zbyt aktualne, a w międzyczasie było sporo zmian w tabeli, to też może być przyczyną tego, że optimizer nie decyduje się użyć odpowiedniego indeksu.

W przypadku akurat tego id - produktów jest około 150k, ale w większości wypadków jest to około 3k rekordów.
A również w przypadku małej ilości rekordów względem całości (czyli 3k rekordów względem 2000k w całej tabeli) odczytywane są wszystkie.

Obecnie zastosowałem rozwiązanie, które wcześniej opisałem, tj.:
1. Pobranie danych z podzapytania i wygenerowanie tabeli tymczasowej - dopiero z niej pobranie danych i posortowanie.

Wydaje się, że jest zdecydowanie szybciej, a przynajmniej jest mniej operacji odczytu całej tabeli.

Może również dobrze byłoby cyklicznie generować tabele para-tymczasowe - tj. nie typowe temporary, a takie, które są w bazie przez kilka godzin i dla wielu sesji połączenia.