Optymalizacja tabeli, zapytania do tabeli z rekordami 560000 Opcje

[yaaa]

Witam wszystkich,

Troszke pogrzebałem po formu ale nie znalazłem nic ciekawego w tym temacie (z góry proszę jak ktoś znalazł coś podobnego p podanie linka).
Mój problem jest taki: pracuję od niedawna dla pewnej firmy, która prowadzi portal i wszystko by było ładnie gdyby nie jedna rzecz. Wszelkie dane z portalu są logowane do jednej tabeli ma on już 560 tyś rekordów (ciągle rośnie) i na niej oparte są wszelkie statystyki, które codzeinnie czyta mój szef. Szef musi mieć wszystko na wczoraj wiec jak wykonanie pewnego zapytania trwa np 40 sek i jeszcze przekroczy ttl przeglądarki i nic mu nie wyświetli to się denerwuje.
Ja mam pomysł żeby rozbić to wszysko na lata tzn. log_2004, log_2005, log_2006 itd.
wtedy tabele były by dużo mniejsze i zapytania dla poszczególnych lat trwały by krócej tylko, że zapytanie o wszystkie lata znów by było strasznie długie
Oto struktura tabeli:

[SQL] pobierz, plaintext 
CREATE TABLE `wyn_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `addr` varchar(15) NOT NULL DEFAULT '',
  `session_nr` int(11) NOT NULL DEFAULT '0',
  `partner` smallint(5) UNSIGNED NOT NULL DEFAULT '0',
  `user_id` int(11) NOT NULL DEFAULT '0',
  `fv` varchar(5) NOT NULL DEFAULT '',
  `page` varchar(50) NOT NULL DEFAULT '',
  `description` text NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `session_nr_idx` (`session_nr`),
  KEY `description` (`description`(255)),
  KEY `page_idx` (`page`),
  KEY `date_idx` (`date`),
  KEY `description_idx` (`description`(255))
) ENGINE=MyISAM DEFAULT CHARSET=latin2 COMMENT='do logowania najwazniejszych zdarzen w systemie' AUTO_INCREMENT=5057416 ;
[SQL] pobierz, plaintext 

Z góry dzięki za każdy pomysł.

[FiDO]

Podaj jesli mozesz to zapytanie.. byc moze to wlasnie w nim trzeba cos poprawic.

[yaaa]

Podaj jesli mozesz to zapytanie.. byc moze to wlasnie w nim trzeba cos poprawic.

Oto jedno z wielu.

[SQL] pobierz, plaintext 
SELECT description, count(*) AS ile 
FROM wyn_log WHERE to_days(date)>=to_days('".$date_from."') AND to_days(date)<=to_days('".$date_to."') AND page='summary' AND description LIKE '%art_id:%' GROUP BY description ORDER BY ile DESC";
[SQL] pobierz, plaintext 

Tylko że ja mam takich zapytań kilkadzieiąt lub nawet kilkaset, bo jak wspomniałem wszelkie statystyki są oparte na tej tabeli a nowe ciagle przybywaja. Szef co dziennie chce miec mozliwosc inwigilowania czegos nowego i te zaptanie staja sie coraz to bardziej skomplikowane

[yavaho]

Moim skromnym zdaniem problemem nie jest tu ilosc rekordow w bazie danych, tylko ilość i złożoność zaytan do bazy.
Musisz zoptymalizowac sposob pobierania danych z bazy.
Jezeli te statystyki (dla szefa) sa potrzebne w przeliczeniu co 24godz a nie na zywo to mozesz np przygotowac dodatkowa tabele gdzie beda sie znajdowaly juz gotowe przeliczone wartosci, a aktualizacja tej tabeli bedzie robiona w raz dziennie w nocy.

[FiDO]

Tylko że ja mam takich zapytań kilkadzieiąt lub nawet kilkaset, bo jak wspomniałem wszelkie statystyki są oparte na tej tabeli a nowe ciagle przybywaja. Szef co dziennie chce miec mozliwosc inwigilowania czegos nowego i te zaptanie staja sie coraz to bardziej skomplikowane

To w koncu jedno zapytanie wykonuje sie ~40 sekund czy caly zestaw zapytan generujacych statystyki ? Jesli to pierwsze to raczej jest wina tego konkretnego zapytania.. ale mam przeczucie, ze chodzi o to drugie, zatem na jedno zapytanie przypada juz ulamek sekundy, co jest sensowna wartoscia.
Podobnie jak poprzednik sugeruje jakas metode cache'owania. Ja w swoim obecnym projekcie mam statystyki, ktore sa generowane przez ok 20 zapytan, w sumie niecala sekunda.. a mimo to denerwowal mnie ten lekki przestoj i zdecydowalem sie na cache'owanie ich na godzine. Jesli masz dostep do cron'a to mozesz je generowac z jego poziomu, a na stronie wyswietlac juz statyczne dane, bedzie to chyba najlepsze rozwiazanie.

[yaaa]

To w koncu jedno zapytanie wykonuje sie ~40 sekund czy caly zestaw zapytan generujacych statystyki ? Jesli to pierwsze to raczej jest wina tego konkretnego zapytania.. ale mam przeczucie, ze chodzi o to drugie, zatem na jedno zapytanie przypada juz ulamek sekundy, co jest sensowna wartoscia.

Statystyk jest bardzo dużo i są przeróżne większość z nich składa się z wielu zapytań i rzeczywiście niektóre zapytania są bardzo szybkie, ułamek sekundy niektóre długie.
Ale system cache'owania wydaje mi się bardzo sensownym rozwiązanie gdyż większość z tych statystyk nie jest mu potrzebna na żywo.
To teraz prośba jeżeli już tworzyliście taki system cache'owania to możecie podać mi jego założenie, jak to wygląda?

[FiDO]

Jesli masz dostep do crona to najlepszym pomyslem byloby generowanie statystyk wlasnie za jego pomoca powiedzmy raz na godzine (lub nawet rzadziej) a na stronie juz tylko wczytanie statycznego html'a, wiec bardzo szybka operacja.
Jak nie masz to mimo wszystko cachowalbym calego html'a, tylko teraz nie wiem jak masz ta aplikacje zbudowana.. jesli uzywasz szablonow to moze warto skorzystac z cachowania, ktore one udostepniaja ? W przeciwnym wypadku musialbys sam cos takiego napisac.
W wersji uproszczonej mogloby to wygladac tak, ze sprawdzasz czy pliki z cachem istnieja i sa niestarsze niz godzina. Jesli tak to nie trzeba nic generowac tylko je wczytac.. jesli nie to nastepuje ich generowanie i zapisanie do pliku z cachem.

[SongoQ]

Lepiej jest poprawic zapytanie i czas bedzie mniejszy.
1 to naluz indeksy na warunki w WHERE to powinno Ci troche pomoc, kolejna rzecza to pomysl jak sie pozbyc LIKE.

[yaaa]

kolejna rzecza to pomysl jak sie  pozbyc LIKE.

Pozbycie się LIKE nie będzie takie proste gdyż ten system loguje już od 3 lat równie dorze mógłbym go przepisać od nowa a to mi sie nie oplaca i nie mam na to czasu.

Co do systemu cash'owania to:
1. bardzo dużo statystyk to statystyki dzienne, czy więc zrobić np: tabele gdzie bedę miał po kolei wszystki dni dajmy na to od 3 lat i nowe dodawal i do nich przypisane pola z poszczególnymi wynikami.
2. Co gdy duzo statystyk już mi się nie wczytuj bo skrypt wykonuje się zbyt długo, jeżeli nawet zrobie skrypt cash'ujący to czy on się wykona (ok. mogę zoptymalizować niektóre zapytania ale LIKE się nie pozbędę wszędzie)

[SongoQ]

LIKE mozesz zamienic na tabele posrednie, gdzie jak juz FiDO wspominal bedzie odpalane przez crona .

[yaaa]

Wielkie dzięki dla was chłopaki (Fido i SongoQ) za pomoc.
Sprawę rozwiązałem w ten sposób:
Zrobiłem tabele pośrednie gdzie podzielilem dane z tabeli z logami (5 mln rekordów a nie 500 tys zer nie doliczyłem ), czas wykonywania przecietnego zapytania spadł z 40 sekund do ok 1,5 sek. Ale, że zapytań czasem jest wiele i tak by to trwało troche dlatego z tabel pośrednich co godzinę wyciągam statystyki do pliku a potem już statyczny html na stronie. Narazie co prawda zrobiłem to tylko dla jednej rzeczy a jest ich kilkanaście ale schemat już jest i reszta to tylko wklepanie kodu z małymi zmianami. Szef się cieszył jak dziecko, że nie musiał czekać wieków na statystyki.
Jeszcze raz dzięki i pozdrawiam