[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

[pmir13]

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.

Ten post edytował pmir13 3.05.2013, 09:35:41