Witam mam tabele z danymi w której jest ponad 3 500 000 rekordów, zajmuje ona około 850 mb, posiada 60 kolumn różnego typu varchar, date, time, decimal, int, mam poustawiane indeksy na kolumny wg których wybieram rekordy za pomocą where, nawet dodałem partycjonowanie danych wg miesiąca z którego pochodzi rekord, jednak pomimo tego zapytanie zwracające 20 rekordów posortowanych z where ustawionym na 6 kolumnach wykonuje mi sie 18 sekund ,

jakie mam opcje żeby to przyspieszyć ? w tej chwili działa mi to na VPS mini z superhosta

Hmm, VPS...

Przydałoby się więcej RAM-u, żeby to sensownie chodziło, bo pewnie już zaczęło swapować albo odczytuje bezpośrednio z dysku...

prawdę mówiąc na początek nie chciał bym się w koszty dedyka pakować, wiec chętnie bym sie dowiedział

czy np. podzial tabeli na 2 tabele w ktorych w jednej będą kolumny po których szukam a w drugiej reszta ma sens ? czy tez niewiele dzięki temu osiągne ?

To trochę za dużo ci zajmuje.
Masz za dużo indexów i za długie typy zmiennych. Jeżeli masz int(11) a używasz tylko 8 znaków - to zmniejsz do 8.
Do tego - popakuj klucze (PACK_KEYS - znajdziesz na zakładce operacje w phpmyadmin-ie).
Stwórz tylko takie klucze, które ci są potrzebne - niezbędne. Za dużo kluczy zabija bazę. Dodatkowo - jeżeli robisz klucze z kilku kolumn to zwróć uwagę
na kolejność. Pierwsze w indexie powinno być pole z największą liczbą powtórzeń (np jeżeli masz datę w kliku polach dzien|miesiac|rok - to klucz powinien być :

[SQL] pobierz, plaintext 
ALTER TABLE `new` ADD INDEX ( `rok` , `miesiac` , `dzien` , `id`) ;
[SQL] pobierz, plaintext 

a na pewno nie:

[SQL] pobierz, plaintext 
ALTER TABLE `new` ADD INDEX ( `id`, `dzien` , `miesiac` , `rok`  ) ;
[SQL] pobierz, plaintext 

Huh? INT to typ numeryczny a nie znakowy


@sv8: EXPLAIN przed zapytaniem, wklejasz tutaj wynik i popatrzymy.

Jak ma taką bazę to cudów nie będzie - musi być długo, a co dopiero pod obciążeniem? Paru userów i baza zapchana na amen. Jeśli nie masz dedyka pod bazę to rozsądne wydaje się ograniczanie tabel do miliona rekordów. Jak to zrobić? Pomysłów jest wiele, można podzielić wg daty, województwa, kolejności dodawania...

partycjonowanie już dodał

Tabela zawiera 2 329 013 rekordów, waży 582 mb, powyższy select wykonuje sie ok 10 sekund

Tak z ciekawosci czy Postgres lepiej by sobie z tym poradził, czy to kwestia ograniczeń serwera i po prostu niewiele da sie z tym zrobić

No i na początek widać, że żadnych indeksów to zaputanie nie wykorzystuje.
Pokaż jakie indeksy pozakładałeś.

Nie wiem dlaczego wszyscy cokolwiek się wypowiadają jeżeli nie znamy struktury tabeli, indeksów oraz nie mamy paru przykładowych zapytań na podstawie których tworzy się indeksy. Więc podaj nam wszystkie potrzebne dane.

Tak wygląda struktura tabeli

[SQL] pobierz, plaintext 
CREATE TABLE `data` (
  `op` varchar(4) CHARACTER SET utf8 COLLATE utf8_polish_ci NOT NULL,
  `date` date NOT NULL,
  `period` tinyint(2) NOT NULL,
  `catalog` varchar(4) CHARACTER SET utf8 COLLATE utf8_polish_ci NOT NULL,
  `template` text CHARACTER SET utf8 COLLATE utf8_polish_ci NOT NULL,
  `tid` varchar(50) CHARACTER SET utf8 COLLATE utf8_polish_ci NOT NULL,
  `hcode` varchar(8) CHARACTER SET utf8 COLLATE utf8_polish_ci NOT NULL,
  `destCode` varchar(100) CHARACTER SET utf8 COLLATE utf8_polish_ci NOT NULL,
  `city` varchar(40) CHARACTER SET utf8 COLLATE utf8_polish_ci NOT NULL,
  `region` varchar(30) CHARACTER SET utf8 COLLATE utf8_polish_ci NOT NULL,
  `country` varchar(30) CHARACTER SET utf8 COLLATE utf8_polish_ci NOT NULL,
  `name` varchar(40) CHARACTER SET utf8 COLLATE utf8_polish_ci NOT NULL,
  `category` decimal(2,1) NOT NULL,
  `hr` varchar(3) CHARACTER SET utf8 COLLATE utf8_polish_ci NOT NULL,
  `hrd` varchar(40) CHARACTER SET utf8 COLLATE utf8_polish_ci NOT NULL,
  `hbb` varchar(1) CHARACTER SET utf8 COLLATE utf8_polish_ci NOT NULL,
  `hm` varchar(1) CHARACTER SET utf8 COLLATE utf8_polish_ci NOT NULL,
  `hmd` varchar(30) CHARACTER SET utf8 COLLATE utf8_polish_ci NOT NULL,
  `minA` tinyint(1) NOT NULL,
  `maxA` tinyint(1) NOT NULL,
  `minP` tinyint(1) NOT NULL,
  `maxP` tinyint(1) NOT NULL,
  `defP` tinyint(1) NOT NULL,
  `hmc` varchar(2) CHARACTER SET utf8 COLLATE utf8_polish_ci NOT NULL,
  `hbp` varchar(20) CHARACTER SET utf8 COLLATE utf8_polish_ci NOT NULL,
  `fac` varchar(20) CHARACTER SET utf8 COLLATE utf8_polish_ci NOT NULL,
  `fad` varchar(20) CHARACTER SET utf8 COLLATE utf8_polish_ci NOT NULL,
  `fno` varchar(16) CHARACTER SET utf8 COLLATE utf8_polish_ci NOT NULL,
  `ffmc` varchar(2) CHARACTER SET utf8 COLLATE utf8_polish_ci NOT NULL,
  `dc` varchar(3) CHARACTER SET utf8 COLLATE utf8_polish_ci NOT NULL,
  `dd` varchar(30) CHARACTER SET utf8 COLLATE utf8_polish_ci NOT NULL,
  `drc` varchar(30) CHARACTER SET utf8 COLLATE utf8_polish_ci NOT NULL,
  `ddd` date NOT NULL,
  `ddt` time NOT NULL,
  `dad` date NOT NULL,
  `dat` time NOT NULL,
  `dec` varchar(3) CHARACTER SET utf8 COLLATE utf8_polish_ci NOT NULL,
  `ded` varchar(30) CHARACTER SET utf8 COLLATE utf8_polish_ci NOT NULL,
  `defc` varchar(30) CHARACTER SET utf8 COLLATE utf8_polish_ci NOT NULL,
  `derc` varchar(30) NOT NULL,
  `dedd` date NOT NULL,
  `dedt` time NOT NULL,
  `dead` date NOT NULL,
  `deat` time NOT NULL,
  `pa` decimal(10,2) NOT NULL,
  `pac` varchar(5) CHARACTER SET utf8 COLLATE utf8_polish_ci NOT NULL,
  `pc1` decimal(10,2) NOT NULL,
  `pc1af` tinyint(4) NOT NULL,
  `pc1at` tinyint(4) NOT NULL,
  `pc1c` varchar(5) CHARACTER SET utf8 COLLATE utf8_polish_ci NOT NULL,
  `pc2` decimal(10,0) NOT NULL,
  `pc2af` text NOT NULL,
  `pc2at` tinyint(4) NOT NULL,
  `pc2c` varchar(10) NOT NULL,
  `pc3` decimal(10,0) NOT NULL,
  `pca3af` tinyint(4) NOT NULL,
  `pc3at` tinyint(4) NOT NULL,
  `pc3c` varchar(10) NOT NULL,
  `pc4` decimal(10,0) NOT NULL,
  `pc4af` tinyint(4) NOT NULL,
  `pc4at` tinyint(4) NOT NULL,
  `pc4c` varchar(10) NOT NULL,
  KEY `location` (`country`,`region`,`city`,`hcode`),
  KEY `date` (`date`),
  KEY `period` (`op`,`period`),
  KEY `persons` (`minA`,`maxA`,`minP`,`maxP`,`defP`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=1 DELAY_KEY_WRITE=1;
[SQL] pobierz, plaintext 

OK. Spróbuj takiego indeksu:
(`date`, `minA`,`maxA`,`minP`,`maxP`,`defP`)

A w zapytaniu warunek z datą przestaw na początek (tak żeby kolejność kolumn w zapytaniu zgadzała się z kolejnością kolumn w indeksie):


i EXPLAIN

P.S. ten kawałek

jakiś dziwny jest. Niby cztery razy to samo, ale pole *af raz jest tinyint, a raz text. Pole *c raz varchar(5) raz varchar(10). Gdyby nie to, to wyglądałoby na to, że te kolumny nadają się do znormalizowania i wydzielienia do innej tabeli.

praktycznie bez zmian w porównaniu do wczesniejszego selecta a explain zwraca to samo co wyżej

może i tak tylko później miałem zamiar filtrowania po tych wartosciach, nic faktycznie będe musiał to rozbić na kilka tabel moze wtedy bedzie to jakoś działać

O to dziwne. Bo nawet jeżeli nie skorzystałby z tego nowego indeksu, to powinien wziąć sobie przynajmniej Twój indeks `date`.

Tak nawiasem mówiąc, ta tabela ma jakiś klucz główny w ogóle? Wprost żaden nie jest zdeklarowany.

kiedyś miała ale ze względu na partycjonowanie ale prawdę mówiąc nie pełnił on żadnej funkcji a i partycjonowanie nie dawało rezultatów wiec bylem ciekaw czy wywalenie go coś zmieni i jak się okazało niewiele zmieniło

Kolega cos ma wspolnego z Merlinem, Securem, BlueWendo? Jesli tak, to proponuje to co masz rozbic na parenascie tabel: trip, rooms, price, city, region, country, departure.

Tym sposobem tabela z glownymi IDkami bedzie zajmowala 30-60MB i bedzie zapierniczala szybciej. Szukanie nie po np " EGIPT" a po ID_COUNTRY. I jak ktos wczesniej wspomnial Partycjonowanie

Tak merlin, tak tez zrobiłem podzieliłem to na mniejsze tabele.

maly_swd a mógłbyś podać na czym uruchamiałeś coś takiego, chodzi mi o liczby jak duza baza była jaki serwer i jak to działało

Jak dla mnie masz bardzo dużo nadmiarowych danych

pola: city, region, country etc. można wrzucić do oddzielnych tabel i łączyć joinami po id...

Optymalizacja i tworzenie tabeli zgodnie ze sztuką to jedno, ale taką "kobyłę" i tak ja bym podzielił, aż się prosi zrobić każdy region w oddzielnej tabeli i przy jakieś szukajce wybór regionu dać obligatoryjnie, przy kilku milionach rekordów na zwykłym hostingu nie będzie szybko chodzić choćby całe php.pl optymalizowało

i dla tych trzech mam osobne tabele ale dla każdego pozostałego większego pola tworzenie słownika to chyba popadanie w przesadę poszukam jakiegoś lepszego hostingu, dam więcej pamięci w configu i powinno być ok

a tak z ciekawości jest jakiś MySQLowy sposób na automatyczne tworzenie słowników? ze ja je dodaje w formie tekstowej np Polska a serwer sam się tym zajmuje sam mi podmienia to na np 1, a jak nie ma tego w słowniku to sam dodaje ? triggery ?

procedury
mysql nie ma triggerów instad off

Triggery są od wersji 5.1

dodałem magiczne sformowanie "instead of" - czyli trigger wykonujący insert zamiast normalnego insertu
w MSSQL-u wygląda to mniej więcej tak:

[SQL] pobierz, plaintext 
CREATE TRIGGER tr_multiTest_io ON multitest instead of UPDATE AS
BEGIN
    SELECT 'instead of trigger firing'
    DELETE multiTest
    FROM   multiTest INNER JOIN deleted ON multiTest.keyVal = deleted.keyVAl
 
    INSERT  multiTest
    SELECT * 
    FROM   inserted
END -- trigger def
[SQL] pobierz, plaintext 

czytaj chcesz wstawić coś do tabeli za pomocą dowolnej konstrukcji INSERT, a baza to przechwyci, trzerobi do innej postaci i doda dane zupełnie gdzie indziej

jak zrobisz to na tigerach to gwarantuje, ze sie nie wyrobisz z updatem w 24h:)

Merlin ma dobre xmle ale operatorzy roznie wpisuja city region country wiec nie zrobisz tego slownikowo, trzeba recznie robic grupowania itp.
Czasem jest Egipt, egipt itp.
W regionach jest masakra.

zycze powodzenia:)

Ponieważ?

xmle maja okolo 80gb
tripow jest okolo 40mln
i tyle samo sprawdzen

Próbowałeś kiedyś zrobić masę insertów w transakcji? Uwierz, że na 100% dałyby radę w mniej niż 24h

Takie pytanko - a próbowałeś puszczać to zapytanko na localhost-cie - na swoim kompie??

Poświęć chwilę, wrzuć to na dysk swojego kompa i sprawdź jak jest.
Zrobiłem taką tabelkę na swoim G wartym laptopie HP i wrzucam właśnie losowe dane.
Dobiłem już do 420 MB danych i twoje zapytanko śmiga "Pokaż rekordy 0 - 29 (1 039 wszystkich, Wykonanie zapytania trwało 0.0014 sekund(y)) "

... jakiś czas później ...

Dobiłem już do 2,5 GB - nadal nie mam tych 10 sec - raczej poniżej 1sec

Wookieb-> zobacz jak wyglada XML od Merlina i co jest potrzebne, a pozniej mozemy dyskutowac:).
Przy normalnych insertach to spokojnie w 1-3h sie da zrobic... wiec masz racje.

Podaj adres do tego xml-a. Nie mam do tego dokumentacji.

niestety nie moge udostepniac tych XML, sa platne:(

i samo sprawdzenie miast regionow kraju, miejsca wylotu, obiektu, rodzaju zakwaterowania: przy robieniu updatu raz na 24h + do tego raz na 1h sa incrementale (czyli dane przyrostowe)... mialem wczesniej napisane to w sposob "tak jak powinno byc" i przy takiej ilosci danych niestety nie wyrabialo sie.

obecnie mam 40mln rekordow (kolega ma pewnie mniejsza ilosc XMLi do przetrawienia, ale warto pomyslec na przyszlosc).

pozdrawiam

ps. nie neguje Twojego rozwiazania - bo jest dobre... lecz nie w tym przypadku