Problem z zapytaniem i brak pomysłu na jego optymalizacje... Opcje

[sniver]

Najchętniej to w takim momentach był zrobił to: (IMG:style_emoticons/default/sciana.gif)

Mam zapytanie i baze, niby wszystko ok, ale wykonuje sie troche długo bo baza produktów troche w EroBAY urosła...

Zapytanie jest z wyszukiwarki, a konkretnie przyporządkowuje kategorie produktów względem znalezionych w bazie danych...

Oto query:

[SQL] pobierz, plaintext 
SELECT                                  
	`c`.`CAT_Id` AS `id`,
	`c`.`PARENT_Id` AS `parent`,
	`c`.`CAT_Name` AS `name`,
	`c`.`CAT_Url` AS `url`,
	(
		SELECT
			COUNT(1)
		FROM
			`products` AS `p`
				JOIN `productsdescription` AS `pd` ON (`pd`.`PRD_Id` = `p`.`PRD_Id`)
		WHERE
			(`p`.`CAT_Id` IN (
				SELECT
					`c2`.`CAT_Id`
				FROM
					`categories` AS `c2`
				WHERE
					(
						(`c2`.`CAT_Id` = `c`.`CAT_Id`)
						OR (`c2`.`CAT_Ip` LIKE CONCAT(`c`.`CAT_Id`,'.%'))
					)
					AND (
						(`c2`.`CAT_Active` = 1)
						AND (`c2`.`CAT_Counter` > 0)
					)
				)
			)
			AND (MATCH(`pd`.`PRD_Name`, `pd`.`PRD_Description`) AGAINST ('kostium' IN BOOLEAN MODE))
		) AS `licznik`
 
FROM
	`categories` AS `c`
 
WHERE                                   
	(
		(`c`.`CAT_Active` = 1)
		AND (`c`.`CAT_Counter` > 0)
	)
	AND (
		(`c`.`CAT_Id` = 61)
		OR (`c`.`PARENT_Id` = 61)
		OR (`c`.`CAT_Ip` LIKE CONCAT(`c`.`CAT_Id`,'.%'))
		OR (`c`.`PARENT_Id` = 3)
	)
 
GROUP BY `c`.`CAT_Id`
 
HAVING
	(`licznik` > 0)
 
ORDER BY `c`.`PARENT_Id` ASC, `c`.`CAT_Sort` ASC
[SQL] pobierz, plaintext 

Problem znajduje się w subzapytaniu zwracającym wartość licznik, a konkretnie w kolejnym zagnierzdzeniu które wpisuje do IN podkategorie..

Może ktoś ma jakiś fajny pomysł jak to ugryźć...

[Noidea]

Z tego co widzę to drzewko kategorii jest zrobione w "sprytny" sposób przez zapisanie ID w stringu ( "2.34.232.3422" ). Nazwanie kolumny CAT_Ip i oddzielanie liczb kropkami nie sprawia, że rozwiązanie jest bardziej profesjonalne, równie dobrze można oddzielać je przecinkami.
Taką strukturę stosują ludzie, którym bardziej zależy na łatwości zapisania zapytania ( WHERE ip LIKE id + '.%' ) niż na wydajności - czyli masz to co chciałeś (IMG:style_emoticons/default/smile.gif)

Najlepsze rozwiązanie to google -> mysql category tree, przeanalizować kilka rozwiązań, wybrać najlepsze, przerobić strukturę bazy.


Rozwiązania doraźne:
Widzę, że w podzapytaniu zakładasz, że CAT_Id jest zawsze na początku CAT_Ip. Może da się to wykorzystać, wstawić jakąś dodatkową kolumnę do tabeli categories z powtórzonymi danymi, zamienić skorelowane podzapytanie na JOINa, cokolwiek. Tylko najpierw muszę wiedzieć co jest w kolumnie CAT_Ip. A dokładnie to ID której kategorii jest na samym początku.

[sniver]

CAT_Ip to tz. IP kategorii :-)
w takim formacie jak to napisałeś ;>

właśnie pomyślałem o tym że do każdej kategorii mogę dodać pole (jak wspomniałeś) i tam zapisać wszystkie dzieci i dzieci, dzieci jakiejś kategorii

Przeanalizowałem kilka metod na robienie drzewa IP. Powyższa metoda została wykorzystana na www.erobay.pl - więc wiadome do czego to ma być.
W/g waszego doświadczenia jaka struktura drzewa kategorii może być najbardziej optymalna?

Ten post edytował sniver 29.09.2010, 11:28:00

[Pilsener]

Taką strukturę stosują ludzie, którym bardziej zależy na łatwości zapisania zapytania ( WHERE ip LIKE id + '.%' ) niż na wydajności

- nie sądzę. Pokaż mi bardziej wydajną metodę pozwalającą pobierać, przenosić, usuwać wybrane gałęzie. Poza tym pola "IP" czy depth dodawane są pomocniczo właśnie w tym celu.

Proponuję zrobić explain i utworzyć odpowiednie indeksy, powinno pomóc. Jeżeli problemem jest rozmiar drzewa to można je podzielić na kilka mniejszych.

P.S. > Możesz też poeksperymentować z modelem nested set, ale jak patrzę na te pętle w zapytaniach to nie wiem, czy to będzie wygodne i wydajne...

[sniver]

Dodałem dodatkowe pole. Pole o dzwięcznej nazwie "Collection" - a konkretnie CAT_Collection, czyli zbiór :-)

Każda z kategorii ma swoje dzieci (a dzieci mają dzieci). System każdorazowo musi odpytywać bazę o to które to dzieci należą do danej kategorii, w tym fragmencie:

[SQL] pobierz, plaintext 
[...]
			(`p`.`CAT_Id` IN (
				SELECT
					`c2`.`CAT_Id`
				FROM
					`categories` AS `c2`
				WHERE
					(
						(`c2`.`CAT_Id` = `c`.`CAT_Id`)
						OR (`c2`.`CAT_Ip` LIKE CONCAT(`c`.`CAT_Id`,'.%'))
					)
					AND (
						(`c2`.`CAT_Active` = 1)
						AND (`c2`.`CAT_Counter` > 0)
					)
				)
			)
 
[...]
[SQL] pobierz, plaintext 

Powyższe rozwiązanie zamieniłem na dodanie informacji o spisie kategorii do pola: 'CAT_Collection', a w nim informacje w stylu 5, 94, 115, 116

Teraz gotowy zbiór potomków wpisuję do tego warunku i wygląda tak: (`p`.`CAT_Id` IN (`c`.`CAT_Collection`)) - różnica w działaniu? Piorunująca :]
Czy to dobre czy nie - dam znać za tydzień jak przybędzie odwiedzających i baza produktów sie zwiększy na EroBAY.pl :-)

Ten post edytował sniver 1.10.2010, 12:20:30