Zliczanie produktów z kategori i podkategori i pod-podkategorii, ... Opcje

[sniver]

Mam zapytanie, a w nim ID kategorii, podkategorii i podpodkategorii itd..

Zapytanie działa, ale wolno mimo iż mam klucz INDEX na pola: CAT_Id, PRD_Deleted, PRD_Active

[SQL] pobierz, plaintext 
SELECT
	ceil(count(0)/10) AS `licz`
 
FROM
	`products` AS `p`
 
WHERE
	(`p`.`CAT_Id` IN (530, 531, 532, 535, 538, 563, 566, 567, 1454, 1455, 1456, 1457, 1459, 1469, 1471, 1472, 1479, 1487, 1488, 1489, 1490, 1491, 1492, 1493, 1494, 1495, 1496, 1497, 5439, 5440, 5441, 5442, 5443, 5444, 5445, 5447, 5449, 5450, 5451, 5452, 5453, 5454, 5455, 5458, 5459, 5460, 5461, 5462, 5463, 5464, 5465, 5466, 5467, 5468, 5469, 5470, 5471, 5472, 5473, 5474, 5475, 5477, 5478, 5479, 5480, 5521, 5523, 5525, 5527, 5529, 5530, 5532, 5533, 5534, 5537, 5540, 5541, 5542, 5543, 5544, 5545, 5546, 5547, 5549, 5550, 5551, 5552, 5553, 5554, 5555, 5556, 6155, 9307, 10780, 10781, 10782, 10783, 11068, 11069, 15550, 15551, 15552, 15553, 15554, 15555, 15556, 15557, 15559, 15561, 15562, 15566, 15568, 15569, 15570, 15573, 15575, 15576, 15577, 15578, 15579, 15580, 15581, 15582, 15583, 15584, 15585, 15586, 15587, 15838, 15839, 15840, 15841, 17893, 17894, 19898, 19899, 19901, 19903, 19904, 19905, 19906, 19907, 19908, 19909, 19910, 19911, 19912, 19913, 19915, 19916, 19917, 19918, 19919, 26914, 27000, 27838, 27866, 27867, 27868, 27869, 27981, 46023, 46024, 47029, 47580, 48883, 48893, 50290, 50331, 50332, 50333, 52260))
	AND (`p`.`PRD_Deleted` = 0)
	AND (`p`.`PRD_Active` = 1)
[SQL] pobierz, plaintext 

ID kategorii wstawiłem do IN bo uznałem że tak powinno być dobrze ale przy bazie która zawiera około 65k produktów (tabela products, typ: InnoDB) działa troche za wolno, a przynajmniej nie tak jak bym chciał (IMG:style_emoticons/default/smile.gif)

Ma ktoś pomysł jak to inaczej zmontować?

Ten post edytował sniver 18.05.2010, 07:34:26

[croc]

To:

[SQL] pobierz, plaintext 
IN (530, 531, 532, 535, 538, 563, 566, 567, 1454, 1455, 1456, 1457, 1459, 1469, 1471, 1472, 1479, 1487, 1488, 1489, 1490, 1491, 1492, 1493, 1494, 1495, 1496, 1497, 5439, 5440, 5441, 5442, 5443, 5444, 5445, 5447, 5449, 5450, 5451, 5452, 5453, 5454, 5455, 5458, 5459, 5460, 5461, 5462, 5463, 5464, 5465, 5466, 5467, 5468, 5469, 5470, 5471, 5472, 5473, 5474, 5475, 5477, 5478, 5479, 5480, 5521, 5523, 5525, 5527, 5529, 5530, 5532, 5533, 5534, 5537, 5540, 5541, 5542, 5543, 5544, 5545, 5546, 5547, 5549, 5550, 5551, 5552, 5553, 5554, 5555, 5556, 6155, 9307, 10780, 10781, 10782, 10783, 11068, 11069, 15550, 15551, 15552, 15553, 15554, 15555, 15556, 15557, 15559, 15561, 15562, 15566, 15568, 15569, 15570, 15573, 15575, 15576, 15577, 15578, 15579, 15580, 15581, 15582, 15583, 15584, 15585, 15586, 15587, 15838, 15839, 15840, 15841, 17893, 17894, 19898, 19899, 19901, 19903, 19904, 19905, 19906, 19907, 19908, 19909, 19910, 19911, 19912, 19913, 19915, 19916, 19917, 19918, 19919, 26914, 27000, 27838, 27866, 27867, 27868, 27869, 27981, 46023, 46024, 47029, 47580, 48883, 48893, 50290, 50331, 50332, 50333, 52260)
[SQL] pobierz, plaintext 

to chyba nie jest najlepszy pomysł. Powinieneś brać JEDEN identyfikator danej kategorii/podkategorii. Jeśli sensownie zaprojektowałeś strukturę tabeli kategorii, to baza "wie" przecież która kategoria należy do której.

[sniver]

no własnie i tu jest problem bo nie wiem jak to zmontować. Przedstawiam strukturę bazy

Tabela z kategoriami:

[SQL] pobierz, plaintext 
CREATE TABLE IF NOT EXISTS `categories` (
  `CAT_Id` int(11) NOT NULL AUTO_INCREMENT,
  `PARENT_Id` int(11) NOT NULL DEFAULT '0',
  `CAT_Ip` varchar(255) NOT NULL,
  `CAT_Name` varchar(155) CHARACTER SET utf8 COLLATE utf8_polish_ci NOT NULL,
  `CAT_Url` varchar(75) NOT NULL,
  `CAT_Sort` int(11) NOT NULL DEFAULT '0',
  `CAT_Counter` int(11) NOT NULL,
  `CAT_Active` int(11) NOT NULL DEFAULT '1',
  PRIMARY KEY (`CAT_Id`),
  KEY `INDEX_GLOWNY` (`CAT_Id`,`PARENT_Id`,`CAT_Counter`,`CAT_Active`),
  KEY `CAT_Id` (`CAT_Id`,`CAT_Ip`),
  KEY `CAT_Ip` (`CAT_Ip`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC AUTO_INCREMENT=52261 ;
[SQL] pobierz, plaintext 

Tabela z produktami:

[SQL] pobierz, plaintext 
 
CREATE TABLE IF NOT EXISTS `products` (
  `PRD_Id` int(11) NOT NULL AUTO_INCREMENT,
  `CAT_Id` int(11) NOT NULL,
  `USR_Id` int(10) UNSIGNED NOT NULL,
  `PRD_DateAdd` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  `PRD_Counter` int(11) NOT NULL DEFAULT '0',
  `PRD_Deleted` smallint(1) NOT NULL DEFAULT '0',
  `PRD_Active` smallint(1) NOT NULL DEFAULT '1',
 
  PRIMARY KEY (`PRD_Id`),
  KEY `KATEGORIA` (`CAT_Id`),
  KEY `USERS` (`USR_Id`),
  KEY `INDEX_GŁOWNY` (`PRD_Deleted`,`PRD_Active`),
  KEY `PRD_Price` (`PRD_Price`),
  KEY `CAT_Id` (`CAT_Id`,`PRD_Deleted`,`PRD_Active`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC AUTO_INCREMENT=85497 ;
[SQL] pobierz, plaintext 

I teraz odrobina wyjaśnienia.
Tabela z kategoriami posiada licznik który się "samoaktualizuje" przez zadanie w Cron'ie.
Struktura oparta jest o nieoptymalny PARENT_Ip i optymalne rozwiązanie tz. drzewo IP. Parent nie jest używany - został tylko w bazie aby łatwiej mi było tym zarządzać...

Produkty są "podpięte" pod ostatnie zagnieżdżenie czyli pod ostatniego potomka.

Przeglądając kategorie brana jest liczba z pola: CAT_Counter - i to działa ok.

Ale aplikacja przy której pracuje ma inne dodatkowe "rozwiązania". Np. Możliwość sparametryzowania produktów (np. kolor (różowy, żółty, zielony itd.), rozmiar (XL, M, S itp.)) i ta część działa cacy...

Ale gdy chcę te produkty rozbić i pokazać do jakich kategorii należą pojawia się problem z czasem wykonywania zapytania...

I nie mam pomysłu jak to inaczej rozwiązać