Witam
Sprawa wygląda tak, mam kilka tabeli tworzę z niego widok, w aplikacji korzystam z tegoż widoku. Pólki nie użyłem w nim dwóch podzapytań wszystko śmigało bardzo szybko, bez żadnych opóźnień. Obecnie czuć zwolnienie, poustawiałem indexy i zwykły SELECT przyspieszył ok 3-krotnie, jednak czas reakcji i tak mnie nie satysfakcjonuje.
Pokażę po krótce na przykładowych tabelach, co potrzebuję i jak tego obecnie używam, zapewne istnieje wydajniejsze zapytanie tworzące taki widok.
Więc mamy 5 tabel, z czego jednej muszę użyć 2 krotnie
`users`, `products`,`category`,`comments`,`history`
Co potrzebuję wyciągnąć z bazy? ..oczywiście najwięcej jak się da, w obecnym przypadku są to wszystkie informacje z tabeli `products` z tabel `category` potrzebuję wyciągnąć id oraz nazwę kategorii głównej oraz nazwę i id podkategorii. Wszystkie kategorie wraz podkategoriami znajdują się w tej tabeli, jest tylko jeden stopień zagnieżdżania, czyli produkt może znajdować się tylko w kategorii głównej, lub w jednej z jego podkategorii. Do tego wyciągam id użytkownika odpowiedzialnego za dany produkt. Do tego momentu wszystko chodziło cacy, wszystko zrobiłem na LEFT JOIN’ach. Zapytanie wyglądało mniej więcej tak (zostało oskrobane z innych danych, które nie są istotą problemu)

[SQL] pobierz, plaintext 
SELECT `p`.`id` `id`, 
`p`.`name` `name`, 
`p`.`pcid` `pcid`, 
`p`.`cid` `cid`, 
`pc`.`name` `pcname`, 
`c`.`name` `cname`, 
`u`.`id` `uid` 
FROM `products` `p` 
LEFT JOIN `category` `pc` ON `p`.`pcid`=`pc`.`id` 
LEFT JOIN `category` `c` ON `p`.`cid`=`c`.`id` 
LEFT JOIN `users` `u` ON `p`.`uid`=`u`.`id`
[SQL] pobierz, plaintext 

Takie zapytanie pięknie śmigało, SELECT 1000 rekordów z takiego widoku z indexami ustawionymi tylko i wyłącznie na ID każdej tabeli trał ok 0,01s.
Niestety potrzebowałem rozbudować widok o kolejne 2 pola, pierwsze z nich to ilość komentarzy do każdego z produktów, drugie to liczba wyświetleń danego produktu z ostatnich 30 dni.
Nie znam sposobu na LEFT JOIN, aby zwrócił mi COUNT owych rekordów, więc w obu przypadkach skorzystałem z podzapytań i tak zapytanie zostało rozbudowane o takie podzapytania:
Ilość komentarzy:

[SQL] pobierz, plaintext 
(SELECT COUNT(`co`.`id`) FROM `comments` WHERE `p`.`id`=`co`.`pid`) `comCount`
[SQL] pobierz, plaintext 

Ilość klików z ostatnich 30 dni

[SQL] pobierz, plaintext 
(SELECT COUNT(`h`.`id`) FROM `history` WHERE DATE_ADD(`h`.`date`, INTERVAL 30 DAY)>=NOW() AND `h`.`oid`=`p`.`id`) `last30daysClicks`
[SQL] pobierz, plaintext 

…w rezultacie otrzymałem takie oto zapytanie:

[SQL] pobierz, plaintext 
SELECT `p`.`id` `id`, 
`p`.`name` `name`, 
`p`.`pcid` `pcid`, 
`p`.`cid` `cid`, 
`pc`.`name` `pcname`, 
`c`.`name` `cname`, 
`u`.`id` `uid`, 
(SELECT COUNT(`co`.`id`) FROM `comments` WHERE `p`.`id`=`co`.`pid`) `comCount`, 
(SELECT COUNT(`h`.`id`) FROM `history` WHERE DATE_ADD(`h`.`date`, INTERVAL 30 DAY)>=NOW() AND `h`.`oid`=`p`.`id`) `last30daysClicks` 
FROM `products` `p` 
LEFT JOIN `category` `pc` ON `p`.`pcid`=`pc`.`id` 
LEFT JOIN `category` `c` ON `p`.`cid`=`c`.`id` 
LEFT JOIN `users` `u` ON `p`.`uid`=`u`.`id`
[SQL] pobierz, plaintext 

Niestety SELECT 1000 rekordów z indexami tylko na ID poszczególnych tabel wzrósł z 0,01s ..do nieszczęsnych 0,6s …po ustawieniu odpowiednich indexów na poszczególnych tabelach (klucze obce) czas ten skrócił się do 0,2s ..niestety jakoś mnie to nie satysfakcjonuje bo odnoszę wrażenie że zapewne da się to zrobić sporo wydajniej. Czy ktoś potrafiłby pomóc?

..ok, widzę że ciężko mi będzie wybrać rozwiązanie z gąszczu propozycji ...ogólnie rozwiązanie jest banalne i aż dziw mnie bierze, że dopiero teraz o tym pomyślałem, robię zwykłego LEFT JOIN'a tabeli komentarze, do SELECTA dodaje moje COUNT(`komentarze`) `countComments` ..i po prostu dodaję klauzulę GROUP BY `id` (grupownie po id produktów).

...niestety w testach już tak różowo nie jest, różnicy brak ..praktycznie co do tysięcznej sekundy identyczny czas, za każdym razem 0.109s