Optymalizacja zbyt długiego zapytania SQL

0

Witam serdecznie,
Mam problem z zapytaniem:


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


 

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

0

czy kategoria musi być stringiem? przeciez tam są same cyfry -> zmień na inta - będzie szybciej
czy te stałe są cały czas stałe? czy zmieniają się? jeśli stałe - zapakować do osobnej tabeli
jeśli są zmienne, to pogrupuj je w przedziały, zamiast pisać a=1 OR a=2 OR a=3 OR a=4 OR a=8 OR a=9 OR a=10 można przecież (a>=1 AND a<=4) OR (a>=8 AND a<=10) do tego przedziały posortuj od największego do najmniejszego
założ index na kategoria

0

Może warto zastanowić się nad zmianą OR na IN: http://stackoverflow.com/a/3074731
Ale ciężko cokolwiek powiedzieć nie wiedząc jak wygląda baza. Czy np jest indeks na tej kolumnie?

0
Northwest napisał(a):

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

Zapewne dlatego, że urosła ilość danych, a taki łańcuszek OR jest skrajnie niewydajny. Zapewne nie jest to też pole indeksowane. Zgadza się?

Pomijając fakt wydajności, zapis tego byłby przyjemniejszy, gdyby zastosować kategoria IN ('321312', '32131321', ...)

0

tabela wygląda tak:

 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 ;
0

załóż index na kategoria
przekazuj wartości jako inty tzn bez apostrofów
poza tym tak jak pisali wcześniej, albo IN albo przedziały

0

ja bym walnął "IN" albo dorobił nową tabelkę z tymi idkami i tez "IN"
a najlepiej to zrobić tak o:

 WHERE to_number(kategoria_id) between 523008 and 523999
-- czy jak tam te idki masz, nie chce mi sie patrzec.
0

tylko indeks jaki? Wiele produktów należy do poszczególnych kategorii...

0

Na początek spróbuj tak:

SELECT COUNT(id) AS ile
FROM produkty p
JOIN kategorie k on k.id=p.kategoria AND k.id IN (521115,521269,...)
WHERE ilosc > 0 and enable = '1' and stan = '1' 

Albo:

SELECT COUNT(id) AS ile
FROM produkty p
JOIN ( SELECT id FROM kategorie WHERE id IN (521115,521269,...)) k on k.id=p.kategoria 
WHERE ilosc > 0 and enable = '1' and stan = '1' 
0
 create index nazwa_indexu on produkty(kategoria_id)
0

Ponadto po co bigint na typie tej kolumny? Spodziewasz się przekroczyć dwa miliardy kategorii?

0

Dziękuję za pomoc :-)

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

 

$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] , ";
                                    }
                                }
                            }
                        }
                    }
                }
            }

tabela wygląda następująco:

 

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 ;

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

0

Ale powiedz, czego oczekujesz, bo ten kod jest trochę pomotany. Chcesz wyciągnąć wszystkie podkategorie które są w danej kategorii?
Jeśli tak, to...

SELECT 
    count(f_id) AS ile
FROM
    drzewko_kategorii ctg1,
    drzewko_kategorii ctg2
        INNER JOIN
    produkty ON ctg2.id = produty.kategoria
WHERE ctg1.id=1 AND ctg1.left<ctg2.left AND ctg1.right>ctg2.right

to zapytanie zwróci Ci to co chciałeś, tzn masz jedno zaytanie zamiast tego pierwszego wielkiego, i tego dziwnego kodu z zapytaniami.

1 użytkowników online, w tym zalogowanych: 0, gości: 1