Skrócenie baardzo długiego zapytanie i czasochłonnego zapytania z REGEXP, Optymalizacja zapytania z ponad 20 warunkami z REGEXP i LIKE Opcje

[Sajrox]

Witam,

Posiadam pewien skrypt które musi znaleść produkty podobne do produktu pobranego z xmla.

Aby tego dokonać na początku wykonuję zapytanie szukając po kolei po 3 słowa z danego produktu.

Ogólnie interesuje mnie to czy da się jakoś inaczej wykonać to samo zapytanie aby otrzymać takie same wyniki. Obecne zapytanie potrafi się wykonywać nawet 45 sekund :/

[SQL] pobierz, plaintext 
SELECT p.id FROM pco_product AS p WHERE (p.main = '1') AND (p.symbol REGEXP '([s ]|^)(smiley)([s ]|$)' OR p.name REGEXP '([s ]|^)(smiley)([s ]|$)' OR p.name_filter REGEXP '([s ]|^)(smiley)([s ]|$)') AND (((p.name_filter LIKE '%smiley%') OR (p.name_filter LIKE '%elegancki%' AND p.name_filter LIKE '%smiley%') OR (p.name_filter LIKE '%oldschoolowy%' AND p.name_filter LIKE '%elegancki%' AND p.name_filter LIKE '%smiley%') OR (p.name_filter LIKE '%portfel%' AND p.name_filter LIKE '%oldschoolowy%' AND p.name_filter LIKE '%elegancki%') OR (p.name_filter LIKE '%w%' AND p.name_filter LIKE '%portfel%' AND p.name_filter LIKE '%oldschoolowy%') OR (p.name_filter LIKE '%kolorze%' AND p.name_filter LIKE '%w%' AND p.name_filter LIKE '%portfel%') OR (p.name_filter LIKE '%brazowozoltym%' AND p.name_filter LIKE '%kolorze%' AND p.name_filter LIKE '%w%'))) LIMIT 5001
[SQL] pobierz, plaintext 

Są tutaj głównie warunki z REGEXP i LIKE
Założyłem index na symbol, name oraz name_filter jednoczesnie.
Oraz osobna index na name oraz osobno index na name_filter i osobno na symbol


Co o tym sądzicie ?

Ten post edytował Sajrox 24.01.2009, 07:42:15

[phpion]

Zastosowanie dowolnego ciągu przed i po szukanym ciągu w LIKE, czyli np. pole LIKE '%wartość%' nie będzie powodowało użycia indeksu. Indeks zostanie użyty jedynie gdy określisz tylko dowolny ciąg znaków po szukanym, czyli np. pole LIKE 'wartość%'. Nie wiem jak wygląda sprawa z wyrażeniami regularnymi ale domyślam się, iż podobnie. W związku z tym Twoje zapytanie przegląda dane sekwencyjnie. Dodatkowo ustawienie dużego limitu (5001) również ma wpływ na szybkość zwracania wyniku (naprawdę potrzebujesz aż tylu danych na raz?).

Osobiście sugerowałbym skorzystanie z wyszukiwania pełnotekstowego:
http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html
Zapoznaj się w szczególności z:
http://dev.mysql.com/doc/refman/5.0/en/ful...l-language.html

Pozwoli to na zdecydowane przyspieszenie wykonywania zapytania, samo zapytanie będzie krótsze i prostsze, a dodatkowo będziesz mógł posortować wyniki na podstawie trafności.

[dr_bonzo]

Zmien sposob przechowywania tego XMLa w bazie - tak zebys nie trzymal tam xml'a tylko ladnie poukladane w tabele i rekordy dane. Rozbij tekst na pojedyncze wyrazu lub przechowuj to tak zebys mogl uzyc wyszukiwania pelnotekstowego (patrz poprzedni post)

[Sajrox]

Sprawa wygląda tak że na początku parsuje sobie cały plik XML i dodaje wszystkie wyniki do tabeli products_cache.
Następnie gdy system pobierze już cały plik zaczynam przypisywanie ofert do produktów pobierając je własnie z tej tabeli.

Ogólnie jest tak że FULL TEXT odpada ponieważ potrzebuje InnoDB do transakcji. Ale wpadłem na inny pomysł. Ze stworzę tabelę np products_wyrazy i bede tam wrzucal pojedyncze wyrazy przypisane do odpowiedniego produktu.

Teraz zamiast LIKE mogę uzywac zwykle "=" i tutaj już indexy robią swoje Minusem jest to że przy np 1 000 000 produktach tabela products_nazwy z wyrazami bedzie miala kilka milionjó wyrazów. Chociaż myslę że to nie jest jakiś problem, po wprowadzeniu indexów.

Co o tym myslicie ? Wydajnośc wzrosła dzięki temu że indexy zaczęły działać.

Jeszcze mam pytanko czy szukanie po "=" oraz LIKE 'wartość%'daje takie same rezultaty ? Chodzi o to czy indexy będą tak samo dobrze działały w obu tych przypadkach ?

[dr_bonzo]

% na poczatku : '%costam' uniemozliwia korzystanie z indeksow.
Czyli 'costam%' skorzysta z indeksu.

Tak samo jak z ksiazka telefoniczna: znajdz mi wszystkie nazwiska zaczynające sie od 'Kowa' a potem konczasie sie 'ski' - jak bedziesz to robil i ile czasu ci to zajmie?

[phpion]

Ogólnie jest tak że FULL TEXT odpada ponieważ potrzebuje InnoDB do transakcji.

Za to nie lubię MySQL: albo więzy integralności z refernacjami (InnoDB) albo wyszukiwanie pełnotekstowe (MyISAM). Ale wracając do tematu: możesz przecież całą bazę mieć opartą o InnoDB i dodatkowo utworzyć tabelę o typie MyISAM jedynie do wyszukiwania. Jedyny minus jest taki, że usuwając rekordy z tabeli właściwej (tej na InnoDB) musisz ręcznie wywalić ich odpowiedniki w MyISAM.