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.

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

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 ?

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ę.

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ść?

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

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.

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

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ł

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

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...

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

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.

LIKE ignoruje indeksy

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?

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

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 :)

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.

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.

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ń.

Jeden z głupszych argumentów jakie się podaje. Jest problem? Jest dobre narzędzie do jego rozwiązania? To spokojnie można z tego skorzystać. Wydajnościowo baza danych problemem nie będzie, ale jakościowo już może być.

Chyba, że autor nie może po prostu z tego skorzystać z jakiś względów, wtedy można myśleć nad bazą.

Argument ogólnikowy
Chodzi o to że przy tak małej bazie i nie skomplikowanych operacjach nie ma sensu stawianie kolejnej usługi która będzie się nudziła.
Co innego gdyby już stała lub postawienie dało korzyść dla innych elementów (można gdzieś jeszcze adoptować) to tak.

Nie zawsze jest potrzeba stosowania lepszego rozwiązania tylko dla tego że się da i będzie dobrze.
To tak jak postawienie strony wys. 1000px z 2 grafikami i trochę tekstu na SF2 lub ZF2. Da się, można, będzie działać. Ale po co? Sztuka dla sztuki?

Skorzystaj z api sphinx`a znacznie przyspieszy Ci wyszukiwarka.

si
http://sqlfiddle.com/#!9/cd95de/6/0

@sazin, co innego nie korzysta (wcale) a co innego "W szczególnym przypadku z % na początku"

Czytanie ze zrozumieniem się kłania :) Od samego początku pytałem autora, czy ma wildcard na początku czy nie. Skoro nie ma, to indeks jest jak najbardziej na miejscu.