[mySQL] Sortowanie wg kolumny COUNT Opcje

[starcode]

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

Ten post edytował starcode 2.05.2013, 12:18:53

[mortus]

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.

Ten post edytował mortus 2.05.2013, 13:33:00