Bardzo duża ilości zapytań - jak rozwiązać problem nie tracąc funkcjonalności Opcje

[adam1024]

Posiadam bazę danych miejscowości (+/- 50 000 rekordów ) oraz wyszukiwarkę która w czasie rzeczywistym wykonuje zapytania ( poprzez AJAX'a ).
Zapytanie jest wykonywane do bazy przy każdym keyupie , oraz w jednym czasie są wykonywane 3 zapytania ( ze względu na to że najpierw zależy mi aby były wyświetlane wszystkie miasta które są stolicami województw, a później te które występują w danym województwie (a mają podobną, taką samą nazwę) a na końcu wszystkie inne miasta mające taką samą nazwę.

W jaki sposób mogę zoptymalizować zapytanie do bazy i zmniejszyć ilość zapytań by nie stracić na funkcjonalności.

Ten post edytował adam1024 26.05.2015, 18:43:55

[ctom]

skoro piszesz, że +/- masz skończoną ilość rekordów (lub przynajmniej często nie rotują) to przemyśl i zaplanuj cacheowanie zapytań

[sazian]

w tabeli zapewne masz kolumnę informującą w jakim województwie jest dane miasto oraz czy jest jego stolicą więc zapewne zamiast trzech zapytań wystarczy sortowanie. Ale trudno coś więcej powiedzieć nie wiedząc jak wygląda baza i jaki dokładnie efekt chcesz osiągnąć.

Jak mniemam problemem jest to że zapytania są za wolne ? Więc może są one nieoptymalne ?

[Pyton_000]

Poza tym dodaj opóźnienie na keyup żeby nie szło zapytanie po każdej literce a np. po 300ms niepisania, wtedy pisząc szybciej początek nazwy wyszuka Ci od razu prawie kompletną informację.

[adam1024]

Nie posiadam informacji o tym czy dane miasto jest stolicą, takie rzeczy sprawdzam w switchu po stornie php ( jest x warunków tak żeby przekierować konkretnie zapytanie jeśli przekazana wartość to np. Kraków wtedy przekazuję województwo-> małopolskie, powiat -> kraków, miasto ->kraków. Zapytania są jak najbardziej w porządku, bardzo szybko się wykonują, jednak jest ich bardzo dużo z racji tego że wykonują się one na żywo...

czyli w przypadku gdy użytkownik wpisuje słowo kraków, mamy 6 zapytań ( bo tyle jest liter ):
1-> k
2-> kr
3-> kra
4-> krak
5-> krakó
6-> kraków

do tego wykonanie 3 zapytań czyli:
1: jeśli przeleci przez switcha w php ( czyli miasto jest stolicą ) -> zapytanie o wynik
2: kolejne zapytanie to wyszukiwanie innego krakowa z województwa małopolskiego
3: i jeszcze jedno zapytanie to wyszukanie innego krakowa z poza województwa małopolskiego

co w sumie na jedno zapytanie daje 6*3 =18 zapytań... oraz przy kasowaniu frazy ( keyup ) też działa ten sam mechanizm tylko od tyłu... więc to daje kolejne 18 zapytań

i jak tutaj dokonać optymalizacji tego, zostawiając całkowitą funkcjonalność?

[Skie]

1. Dodaj cache'owanie wyników z bazy danych - np takie rzeczy jak stolice przecież się nie zmienieją prędko, możesz spokojnie zapamiętywać
2. Zainteresuj się cache'owaniem HTTP i/lub reverse proxy
3. Pomyśl, może warto zrobić cache po stronie frontu
4. Dodaj opóźnienie na keyup, niech request wysyła się po jakimś czasie od wciśnięcia klawisza, nie od razu
5. Mówisz, że masz 3 requesty + PHP po środku - przerób to na 1 request

[Xelah]

Jeszcze odnośnie samego zapytania. Jeśli wyszukujesz tylko nazwy miast począwszy od pierwszej litery (czy nie masz wildcard % na początku zapytania), to upewnij się, że masz indeks przynajmniej na 3-4 pierwsze znaki.

[adam1024]

Kod

$query= "SELECT DISTINCT SQL_CACHE woj, pow, msc FROM miasta WHERE pow <> '$pow' AND msc LIKE %s ORDER BY woj ASC, msc ASC LIMIT 500 ";

indeksów raczej nie mam a już napewno nie na 3 pierwsze znaki

[Turson]

Moja propozycja jest taka, żebyś nie odpytywał bazy co znak, a zaciągnął wszystkie dane po pierwszym odpytaniu, zapisał do tablicy i w niej wyszukiwał

[adam1024]

Turson teraz odpytuję co 3 s. po ostatnim keyupie, więc ilość zapytań troszkę się zmniejszy

[Xelah]

Kod

$query= "SELECT DISTINCT SQL_CACHE woj, pow, msc FROM miasta WHERE pow <> '$pow' AND msc LIKE %s ORDER BY woj ASC, msc ASC LIMIT 500 ";

indeksów raczej nie mam a już napewno nie na 3 pierwsze znaki

Nie wiem co jest w %s więc nie wiem czy indeks ma sens czy nie. Pytanie jest proste. Masz wildcard (%) na początku szukanego ciągu znaków czy nie.

Jeśli to Twoja baza to chyba wiesz, czy masz na kolumnie indeks czy nie...

[adam1024]

nie mam % na początku szukanego ciągu znaków, na kolumnie msc indeksu też nie posiadam

[Xelah]

W takim razie, przy 50k rekordach ma sens dodać indeks na kolumnie msc. Coś a'la:

CREATE INDEX mst_idx ON miasta (mst (4));

Możesz poeksperymentować z różnymi długościami. Przy czym do jest tylko detal i jak napisali poprzednicy skup się na usprawnieniach po stronie frontendu.

[sazian]

LIKE ignoruje indeksy

[adam1024]

mam jeszcze jedno pytanie
muszę wybrać jedną wartość z bazy tak jakby główną która będzie na samej górze ( jest to jedna pozycja która wynika z wcześniej przypisanej zmiennej ) oraz później wypisać resztę pozycji poza tą jedną ( już wcześniej wypisana ) - czy lepiej to zrobić na 2 osobnych zapytaniach czy może pobrać wszystkie wartości z bazy a później sprawdzać te warunki podczas wypisywania?

[Pyton_000]

LIKE ignoruje indeksy

Doprawdy?
http://sqlfiddle.com/#!9/cd95de/1

[Xelah]

LIKE ignoruje indeksy

http://dev.mysql.com/doc/refman/5.0/en/index-btree-hash.html

Chyba, że mówimy o jakiejś innej bazie... ale od samego początku odnoszę wrażenie, że jednak o MySQL :)

[pasman]

http://dev.mysql.com/doc/refman/5.0/en/index-btree-hash.html

no przecież tu piszą że używa..


The index also can be used for LIKE comparisons if the argument to LIKE is a constant string that does not start with a wildcard character.


Ten post edytował pasman 29.05.2015, 09:56:22

[Crozin]

Może pomyśl nad wykorzystaniem narzędzia typu Apache Solr, które zdecydowanie lepiej radzi sobie z tego typu zadaniami niż jakakolwiek relacyjna baza danych? W użyciu w sumie bardzo proste narzędzie. Dodatkowo będziesz mógł na potrzeby podpowiadania w ogóle zrezygnować z PHP jako pomostu pomiędzy danymi, a użytkownikiem.

[Pyton_000]

To chyba armata na komara. Wydajnościowo powinno wystarczyć zwyczajne rozwiązanie.

Choć tu można się zastanowić nad optymalizacją struktury tak aby używać mniejszej ilości zapytań.