Optymalizacja zbyt długiego zapytania SQL Opcje

[northwest]

Witam serdecznie,
Mam problem z zapytaniem:

[SQL] pobierz, plaintext 
 
SELECT COUNT(id) AS ile FROM produkty WHERE ilosc > 0 AND enable = '1' AND stan = '1' AND ( kategoria = '521115' OR kategoria = '521269' OR kategoria = '522991' OR kategoria = '523008' OR kategoria = '523009' OR kategoria = '523010' OR kategoria = '523025' OR kategoria = '523026' OR kategoria = '523027' OR kategoria = '522993' OR kategoria = '522992' OR kategoria = '522962' OR kategoria = '522963' OR kategoria = '522961' OR kategoria = '522960' OR kategoria = '522959' OR kategoria = '522958' OR kategoria = '521268' OR kategoria = '523011' OR kategoria = '523012' OR kategoria = '523013' OR kategoria = '523022' OR kategoria = '523023' OR kategoria = '523024' OR kategoria = '523029' OR kategoria = '523030' OR kategoria = '523031' OR kategoria = '523032' OR kategoria = '523034' OR kategoria = '523035' OR kategoria = '522929' OR kategoria = '521267' OR kategoria = '522965' OR kategoria = '523014' OR kategoria = '523015' OR kategoria = '523016' OR kategoria = '523017' OR kategoria = '523018' OR kategoria = '523019' OR kategoria = '523020' OR kategoria = '523021' OR kategoria = '523028' OR kategoria = '521266' OR kategoria = '523036' OR kategoria = '522981' OR kategoria = '522946' OR kategoria = '522945' OR kategoria = '522926' OR kategoria = '521265' OR kategoria = '523033' OR kategoria = '522950' OR kategoria = '522949' OR kategoria = '522948' OR kategoria = '522947' OR kategoria = '522944' OR kategoria = '522943' OR kategoria = '522942' OR kategoria = '522941' OR kategoria = '521260' OR kategoria = '522966' OR kategoria = '522967' OR kategoria = '522979' OR kategoria = '522968' OR kategoria = '522957' OR kategoria = '522956' OR kategoria = '522955' OR kategoria = '521259' OR kategoria = '522970' OR kategoria = '522971' OR kategoria = '522972' OR kategoria = '522973' OR kategoria = '522974' OR kategoria = '522975' OR kategoria = '522976' OR kategoria = '522977' OR kategoria = '522969' OR kategoria = '522954' OR kategoria = '522978' OR kategoria = '521257' OR kategoria = '522924' OR kategoria = '522923' OR kategoria = '522922' OR kategoria = '522913' OR kategoria = '522912' OR kategoria = '522911' OR kategoria = '522900' OR kategoria = '521258' OR kategoria = '522921' OR kategoria = '522920' OR kategoria = '522918' OR kategoria = '522919' OR kategoria = '522917' OR kategoria = '522916' OR kategoria = '522915' OR kategoria = '522914' OR kategoria = '522899' OR kategoria = '522898' OR kategoria = '522897' OR kategoria = '522895' OR kategoria = '522896' OR kategoria = '522894' OR kategoria = '522893' OR kategoria = '522892' OR kategoria = '522891' OR kategoria = '522890' OR kategoria = '522889' OR kategoria = '521261' OR kategoria = '522964' OR kategoria = '522909' OR kategoria = '522908' OR kategoria = '522907' OR kategoria = '522906'.....
 
[SQL] pobierz, plaintext 

Kiedyś to zapytanie działało poprawnie, jednak od jakiegoś czasu przestało.... Według serwerowni "zapytanie jest zbyt długie i wykonuje się 10 sekund".

Czy ma ktoś może jakiś pomysł jak to zoptymalizować?

Bardzo proszę o pomoc,
Northwest

[Turson]

WHERE kategoria IN(1,2,3) itd.

[northwest]

czyli coś takiego:

[SQL] pobierz, plaintext 
 
SELECT COUNT(id) AS ile FROM produkty WHERE ilosc > 0 AND enable = '1' AND stan = '1' AND kategoria IN (521115, 521269, 52299, 523008, 523009, 523025.....)
 
[SQL] pobierz, plaintext 

[SpiritCode]

Jak rozumiem to są jakieś wybrane kategorie tak?
Jesli nie zrób SELECT-WHERE=SELECT
Druga opcja to zamienić te wszystkie OR na IN ARRAY

Wtedy zamiast tego nawiasu masz:

[SQL] pobierz, plaintext 
kategoria IN ('521115', '521269' [..])
[SQL] pobierz, plaintext 

Druga opcja. Jeśli te kategorie są w jakiejś tabeli i spełniają jakiś wspólny warunek możesz zrobić:

[SQL] pobierz, plaintext 
kategoria IN (SELECT kolumna FROM tabela WHERE warunek)
[SQL] pobierz, plaintext 

Np. te numerki to dajmy na to id z tabeli kategorie i muszą spełniać warunek spelnia = 1

[SQL] pobierz, plaintext 
kategoria IN (SELECT id FROM kategorie WHERE spelnia = 1)
[SQL] pobierz, plaintext 

Wygodniejsze bo unikasz dodatkowego ręcznego dopisywania jak dojdzie nowa kategoria

Ten post edytował SpiritCode 30.12.2014, 11:07:30

[Turson]

czyli coś takiego:

[SQL] pobierz, plaintext 
 
SELECT COUNT(id) AS ile FROM produkty WHERE ilosc > 0 AND enable = '1' AND stan = '1' AND kategoria IN (521115, 521269, 52299, 523008, 523009, 523025.....)
 
[SQL] pobierz, plaintext 

Tak. Indeksy na `kategoria` tez mogą pomóc.

[northwest]

tak, to są numery ID poszczególnych kategorii które wybrał użytkownik....

Przepraszam za głupie pytanie, ale które z powyższych zapytań będzie najbardziej optymalne?;-)

tabela z danymi wygląda tak:

[SQL] pobierz, plaintext 
CREATE TABLE IF NOT EXISTS `produkty` (
  `f_id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `nazwa` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `kategoria` bigint(20) DEFAULT NULL,
  `cena` double NOT NULL,
  `opis` text COLLATE utf8_unicode_ci,
  `enable` char(1) COLLATE utf8_unicode_ci NOT NULL,
  `ilosc` int(11) NOT NULL,
  `stan` int(11) NOT NULL,
  UNIQUE KEY `id` (`f_id `),
  FULLTEXT KEY `nazwa` (`nazwa`,`dostepnosc`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1057359 ;
[SQL] pobierz, plaintext 

[SpiritCode]

Skoro mówisz, ze to wybrane kategorie przez użytkownika to bez wątpienia metoda IN (id, id, id [..]).

Metodę z drugim select którą Ci przedstawiłem warto używać jeśli masz np. Kategorie podzielone na kilka grup i każda z nich ma swoją grupę zaznaczoną. Wtedy jeśli szukasz kategorii z danej grupy Dajesz tego SELECT'a z warunkiem na grupę i pobierasz tylko te

[northwest]

Dziękuję za pomoc :-)

Mam jeszcze jedno pytanie, tą listę kategorii generuje sobie takim "dzikim" kodem:

[PHP] pobierz, plaintext 
 
$sssss = null;
    $qwex = null;
    $tablicaA = $ms->query_select("SELECT id, title, parent_id FROM drzewko_kategorii WHERE  parent_id = '" .($zmienna_z_get) . "';");
    if (isset($tablicaA)) {
        $sssss .= " kategoria IN (";
        foreach ($tablicaA as $wynA => $warttA) {
            if ($warttA[parent_id] != "0") {
                $qwex = 1;
                $sssss .= "   $warttA[id] , ";
 
                $tablicaA1 = $ms->query_select("SELECT id, title, parent_id FROM drzewko_kategorii WHERE  parent_id = '$warttA[id]';");
                foreach ($tablicaA1 as $wynA1 => $warttA1) {
                    $sssss .= "  $warttA1[id] , ";
 
 
                    $tablicaA2 = $ms->query_select("SELECT id, title, parent_id FROM drzewko_kategorii WHERE  parent_id = '$warttA1[id]';");
                    foreach ($tablicaA2 as $wynA2 => $warttA2) {
                        $sssss .= "   $warttA2[id] , ";
 
 
                        $tablicaA3 = $ms->query_select("SELECT id, title, parent_id FROM drzewko_kategorii WHERE  parent_id = '$warttA2[id]';");
                        foreach ($tablicaA3 as $wynA3 => $warttA3) {
                            $sssss .= "   $warttA3[id] , ";
 
 
                            $tablicaA4 = $ms->query_select("SELECT id, title, parent_id FROM drzewko_kategorii WHERE  parent_id = '$warttA3[id]';");
                            foreach ($tablicaA4 as $wynA4 => $warttA4) {
                                $sssss .= "   $warttA4[id] , ";
 
                                $tablicaA5 = $ms->query_select("SELECT id, title, parent_id FROM drzewko_kategorii WHERE  parent_id = '$warttA4[id]';");
                                foreach ($tablicaA5 as $wynA5 => $warttA5) {
                                    $sssss .= "   $warttA5[id] , ";
 
 
                                    $tablicaA6 = $ms->query_select("SELECT id, title, parent_id FROM drzewko_kategorii WHERE  parent_id = '$warttA5[id]';");
                                    foreach ($tablicaA6 as $wynA6 => $warttA6) {
                                        $sssss .= "   $warttA6[id] , ";
                                    }
                                }
                            }
                        }
                    }
                }
            }
 
[PHP] pobierz, plaintext 

tabela wygląda następująco:

[SQL] pobierz, plaintext 
 
CREATE TABLE IF NOT EXISTS `drzewko_kategorii` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `parent_id` bigint(20) UNSIGNED NOT NULL,
  `position` bigint(20) UNSIGNED NOT NULL,
  `left` bigint(20) UNSIGNED NOT NULL,
  `right` bigint(20) UNSIGNED NOT NULL,
  `level` bigint(20) UNSIGNED NOT NULL,
  `title` text COLLATE utf8_unicode_ci,
  `type` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=10000 ;
 
[SQL] pobierz, plaintext 

Dałoby się to jakoś poprawić?
Przez ten kawałek kodu skrypt mega długo się generuje

[SpiritCode]

Ależ oczywiście

Pytanie tylko czy chcesz pobrać wszystkie kategorie w górę czy w dół?
Dzięci czy rodziców?

dla wszystkich rodziców danej kategorii

[SQL] pobierz, plaintext 
SELECT  a.id, a.title, a.parent_id FROM drzewko_kategorii a,
                (SELECT b.id, b.title, b.parent_id FROM drzewko_kategorii b,
                    (SELECT c.id, c.title, c.parent_id FROM drzewko_kategorii c WHERE c.id = {$id}) AS foo
                    WHERE b.id = foo.parent_id OR b.id = foo.id)
                AS foo2
                WHERE a.id = foo2.parent_id OR a.id = foo2.id
GROUP BY a.id
[SQL] pobierz, plaintext 

dla wszystkich dzieci:

[SQL] pobierz, plaintext 
SELECT a.id, a.title, a.parent_id FROM drzewko_kategorii a,
			  (SELECT b.id FROM drzewko_kategorii b,
			    (SELECT id FROM drzewko_kategorii c WHERE c.id = {$id}) AS foo
			  WHERE b.parent_id = foo.id OR b.id = foo.id )
			    AS foo2
			WHERE a.parent_id = foo2.id OR a.id = foo2.id
GROUP BY a.id
[SQL] pobierz, plaintext 

Sprawdź czy działają zamiast {$id} podajesz id kategorii dla której szukasz kategorii w górę lub w dół

edit: poprawka pierwszego zapytania. Brakło pól

Ten post edytował SpiritCode 30.12.2014, 13:13:29

[northwest]

dziękuje, działa

chociaż chyba jednak nie do końca ;-)

Jeśli jestem na "najwyższym poziomie" danej kategorii - to nie wyświetla produktów (zastosowałem 2 zapytanie)....
Dopiero jak wybiorę jakiś konkretny parent - do której są przyporządkowane produkty - to się pokazują....


Chciałbym żeby w momencie gdy mam coś takiego:
1. kategoria główna
2. podkategoria
3. grupa
4. podgrupa

To po kliknięciu KATEGORIA GŁÓWNA - widzę wszystkie produkty z poziomu 1,2,3,4. Po kliknięciu na GRUPA - widzę produkty z poziomu 3 i 4....

Nie wiem czy jasno to opsiałem?

[SpiritCode]

To łączysz zapytania.
Zaciągnij sobie wszystkie dzieci dla interesującej Cię kategorii. Za pomocą wyciągniętych z nich ID wyszukaj produkty które takie ID mają

[northwest]

tzn mam zapytanie które generuje mi najpierw te kategoria IN (......) i potem dodaje je to, co wygeneruje się w zapytaniu kategorii do produktów:
select * from produkty where enable = 1 and kategoriaIN(....) i właśnie na najwyższym poziomie nie wyświetla mi produktów - tylko na niższych...

[SpiritCode]

Nie no zapytania które Ci podałem zwracają Ci kategorie od tych co podałeś w dół oraz w górę, łącznie z tą wybrana kategorią

edit: czyli tak ale to mam rozumieć, że nie chcesz wyświetlać produktów z tych kategorii których szukasz dzieci lub rodziców?

Ten post edytował SpiritCode 30.12.2014, 14:02:46