Zapytanie z 3 tabel - optymalizacja Opcje

[o_d]

Mam 3 tabele : products, products_gallery, products_categories

Relacje:
"products" 1 -> wielu "products_gallery"
"products" 1 -> wielu "products_categories"


Chce pobrać wszystkie grafiki produktów, informacje o produkcie z listy produktó∑, któ®e należą do pewnej grupy kategorii.

Moje zapytanie ma postać:

[SQL] pobierz, plaintext 
SELECT p.productID, p.prod_title, p.prod_iconS, p.prod_price, p.prod_url, pg.prod_photo_transp, pg.prod_photo_thmb, pg.prod_photo_id  
FROM products AS p, products_categories AS pc, products_gallery AS pg  
WHERE pc.catID IN (4, 8, 9, 10, 11, 12, 13, 14) AND p.productID=pc.productID AND p.productID=pg.prod_productID AND p.prod_visible='yes' ORDER BY p.prod_date_pub DESC LIMIT 0 , 50
[SQL] pobierz, plaintext 

ale to zapytanie trwa prawie 3 sekundy i dodatkowo "wiesza się" przy większej liczbie kategorii (ponad 10 kategorii).

Struktura tabel:
PRODUCTS:

[SQL] pobierz, plaintext 
CREATE TABLE IF NOT EXISTS `products` (
  `productID` int(11) NOT NULL AUTO_INCREMENT,
  `prod_type` smallint(2) DEFAULT NULL,
  `prod_title` varchar(100) NOT NULL DEFAULT '',
  `prod_desc` text NOT NULL,
  `prod_distributor` int(8) NOT NULL,
  `prod_producent` varchar(40) DEFAULT NULL,
  `prod_url` varchar(300) NOT NULL DEFAULT '',
  `prod_iconS` varchar(100) NOT NULL DEFAULT '',
  `prod_iconB` varchar(100) NOT NULL DEFAULT '',
  `prod_price` decimal(20,2) NOT NULL DEFAULT '0.00',
  `prod_default_price` decimal(20,2) NOT NULL,
  `prod_currency` varchar(5) NOT NULL,
  `prod_colors` text,
  `prod_external` text,
  `prod_date_pub` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `prod_visible` enum('yes','no') NOT NULL DEFAULT 'yes',
  `prod_status` enum('normal','promo') NOT NULL DEFAULT 'normal',
  `prod_rating` enum('yes','no') NOT NULL DEFAULT 'yes',
  `prod_approved` enum('yes','no') NOT NULL,
  `prod_comments` enum('yes','no','logged_in') NOT NULL DEFAULT 'no',
  `prod_cdn` tinyint(1) NOT NULL DEFAULT '0' COMMENT '0=do wgrania',
  PRIMARY KEY (`productID`),
  KEY `prod_distributor` (`prod_distributor`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4764 ;
[SQL] pobierz, plaintext 

PRODUCTS_GALLERY:

[SQL] pobierz, plaintext 
CREATE TABLE IF NOT EXISTS `products_gallery` (
  `prod_photo_id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `prod_color` text NOT NULL,
  `prod_photo_filename` varchar(50) DEFAULT NULL,
  `prod_photo_thmb` varchar(50) DEFAULT NULL,
  `prod_photo_transp` varchar(50) NOT NULL,
  `prod_photo_desc` text,
  `prod_productID` int(8) UNSIGNED NOT NULL,
  `prod_photo_q` int(2) NOT NULL DEFAULT '5' COMMENT '0 - the best',
  `prod_photo_order` float DEFAULT '0',
  `prod_photo_cdn` tinyint(1) NOT NULL DEFAULT '0' COMMENT '0=do wgrania',
  PRIMARY KEY (`prod_photo_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4957 ;
[SQL] pobierz, plaintext 

PRODUCTS_CATEGORIES:

[SQL] pobierz, plaintext 
CREATE TABLE IF NOT EXISTS `project_categories` (
  `catID` int(5) UNSIGNED NOT NULL,
  `proID` int(8) UNSIGNED NOT NULL,
  KEY `catID` (`catID`),
  KEY `proID` (`proID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
[SQL] pobierz, plaintext 

Jakieś propozycje?

Ten post edytował o_d 1.06.2012, 10:35:59

[bpskiba]

To zapytanie wygląda dobrze.
Problem polega na ilości przesyłanych danych. Samo zapytanie zapewne trwa krótko, ale przesłanie wielu obrazków do aplikacji trwa długo z uwagi na ilość danych i tego nie zmienisz.

Rady: mniej obrazków, ajax, obrazki gorszej jakości...

Ten post edytował bpskiba 31.05.2012, 15:45:22

[maly_swd]

Klucze sa? Daj explaina z tego zapytania i pokaz strukture bazy

Ten post edytował maly_swd 31.05.2012, 16:52:45

[Pilsener]

To zapytanie wygląda dobrze.

- na pewno mamy na myśli to samo zapytanie?

[SQL] pobierz, plaintext 
FROM  products AS p, products_categories AS pc, products_gallery AS pg  
[SQL] pobierz, plaintext 

Iloczyn kartezjański tabel rzadko kiedy bywa wydajny... jeśli masz 3 tabele po 10 rekordów to tak, jakbyś pobierał dane z jednej tabeli 1000 rekordów.

Praktykuje się JOINy i podzapytania.

[o_d]

zaktualizowałem opis o strukturę tabelek. Co można z tym zrobić? Proszę o pomoc.

[alegorn]

pilsner:: nie jest to iloczyn kartezjanski, nie potrzeba pisac slowa join by okreslic relacje miedzy tabelami


o_d:: pokaz nam explaina, wtedy bedzie mozna wiecej cos powiedziec.


poza tym - sprawdz co nam tutaj pokazales

p.productID=pc.productID
p.productID=pg.prod_productID

gdzie:
products AS p,
products_categories AS pc,
products_gallery AS pg

przy czym w tabeli products_categories nie ma takiego pola. wiec jesli jest tak jak piszesz - to nie moze dzialac.

brak kluczy na polach ktore laczysz :
products_gallery.prod_productID << tutaj index da ci znaczny wzrost wydajnosci.


wiecej moglbym powiedziec gdy poprawisz/odpowiesz/wkleisz explaina

pytanie, czy uzywasz do czegokolwiek klucza catID ?

[Pilsener]

Chce pobrać wszystkie grafiki produktów, informacje o produkcie z listy produktó∑, któ®e należą do pewnej grupy kategorii.

Zacznijmy od grup kategorii, czyli od początku - stwórz zapytanie, które pobierze ID produktów należących do określonych grup:

[SQL] pobierz, plaintext 
SELECT proID FROM products_categories WHERE grupa=? #tutaj Twoje warunki
[SQL] pobierz, plaintext 

Teraz trzeba użyć tych ID by pobrać produkty:

[SQL] pobierz, plaintext 
SELECT *
FROM
products
WHERE products.productId IN (#tutaj wsadź zapytanie pobierające ID produktów z tabeli z kategoriami)
[SQL] pobierz, plaintext 

Teraz trzeba pobrać grafiki:

[SQL] pobierz, plaintext 
SELECT products.*,group_concat(products_gallery.prod_photo_filename) AS photos
FROM
products
LEFT JOIN products_gallery ON products_gallery.ID_PRODUKTU = products.productId
WHERE products.productId IN (#tutaj wsadź zapytanie pobierające ID produktów z tabeli z kategoriami)
[SQL] pobierz, plaintext 

Tak to widzę na podstawie tego, co napisałeś. Poeksperymentuj z tą strukturą, dodaj indeksy na pola po których szukasz i łączych tabele a powinno śmigać.

[o_d]

Faktycznie podałem niepoprawnąstruktuę tabeli. Oto właściwa:

[SQL] pobierz, plaintext 
CREATE TABLE IF NOT EXISTS `products_categories` (
  `catID` int(5) UNSIGNED NOT NULL,
  `productID` int(8) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
[SQL] pobierz, plaintext 

Zapytanie działa.

Oto co zwraca mi explain:

Kod

id     select_type     table     type     possible_keys     key     key_len     ref                     rows             Extra
1     SIMPLE         pc         ALL         NULL            NULL        NULL    NULL                    4854     Using where; Using temporary; Using filesort
1     SIMPLE         pg         ALL         NULL            NULL        NULL    NULL                    4911     Using join buffer
1     SIMPLE         p         eq_ref     PRIMARY         PRIMARY         4     fancydot2.pc.productID     1     Using where

[bpskiba]

Explain wskazuje na brak wykorzystania indeksów podczas wykonywania zapytania. Czyli musisz eksperymentować z indeksami
To jest główny problem.

Ten post edytował bpskiba 1.06.2012, 21:25:43