Witam

Posiadam stronę WWW, na której użytkownicy wyszukują muzykę. Każda nowa fraza, która jest wyszukiwana jest zapisywana do bazy. W frazach istniejących aktualizowany jest licznik wyszukań. Aktualnie w dość szybkim czasie tabela zapełnia się rekordami, w miesiąc dochodzi nawet do 30-50k nowych rekordów. Przy każdym wyszukaniu cała tabela jest przeszukiwana, przez co MySQL dość mocno obciąża CPU serwera.

Na razie staram się systematycznie czyścić tabele, lecz wolałbym jakieś inne rozwiązanie usprawniające/przyspieszające. Frazy jak i ilości wyszukań wykorzystywane są do chmury tagów, ostatnio wyszukiwanych oraz najlepszych dlatego wolałbym nie czyścić tabel.

Zrzut tabeli

[SQL] pobierz, plaintext 
CREATE TABLE `mp3_search` (
  `search_id` int(11) NOT NULL AUTO_INCREMENT,
  `search_txt` text NOT NULL,
  `search_ile` int(11) NOT NULL DEFAULT '0',
  `search_data` text character SET latin1 collate latin1_general_ci NOT NULL,
  `search_datal` text character SET latin1 collate latin1_general_ci NOT NULL,
  `search_datarss` text character SET latin1 collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`search_id`)
)
[SQL] pobierz, plaintext 

Wykonywane zapytania podczas wyszukiwania muzyki przez użytkownika

[SQL] pobierz, plaintext 
SELECT search_txt mp3_search WHERE search_txt='szukany tytul'
 
jeżeli fraza jest w bazie:
UPDATE mp3_search SET search_ile=search_ile+1, search_datal='".date("Y.m.d H:i")."', search_datarss='".date("D, d M Y G:i:s")." +0000' WHERE search_txt='szukany tytul'
 
jeżeli frazy nie ma w bazie:
INSERT INTO mp3_search(`search_txt`, `search_data`, `search_datal`, `search_ile`, `search_datarss`) VALUES ('szukany tytul', '".date("Y.m.d H:i")."', '".date("Y.m.d H:i")."', '1', '".date("D, d M Y G:i:s")." +0000')
[SQL] pobierz, plaintext 

Po pierwsze - dodaj indeks na pole search_txt. Po drugie - zastanów się nad poprawnością typów pól, jakie zastosowałeś. Wspomniany search_txt powinien być VARCHAR, a pola z datami powinny być typu DATETIME, a nie tekstowego.

Dziękuję za odpowiedź.

Wykonałem następujące rzeczy:

[SQL] pobierz, plaintext 
ALTER TABLE mp3_search MODIFY search_txt VARCHAR(255);
ALTER TABLE mp3_search MODIFY search_data DATETIME;
ALTER TABLE mp3_search MODIFY search_datal DATETIME;
ALTER TABLE mp3_search MODIFY search_datarss DATETIME;
CREATE INDEX index_mp3 ON mp3_search(search_txt(255));
[SQL] pobierz, plaintext 

Czy ma dużą różnicę nadanie indeksu na całe 255 znaków tak jak to wykonałem? Czy zapytania Select należy modyfikować pod nowy indeks?

Z góry dziękuję i pozdrawiam

Ja mam dwie uwagi:

1) Z tego, co przedstawiłeś wynika, że nie masz łączenia tabel. Upewnij się, że rodzaj składowania danych tabeli to MyISAM. Sprawuje się szybciej niż InnoDB w Twoim przypadku, gdzie wyszukujesz w tekście w jednej tabeli.

2) VARCHAR zmień na CHAR. Różnica jest taka, że pola typu CHAR są stałej długości i nie potrzeba kolejnych obliczeń, aby znaleźć dalsze pola. Minus taki, że CHAR za każdym razem rezerwuje 255 bajtów danych, podczas gdy VARCHAR tyle, ile zajmuje tekst. Tańsze jest dokupienie 100 GB HHD/SSD niż dokupienie 1 GHz procesora. Ta zamiana ma sens tylko wtedy, gdy rodzaj składowania danych to MyISAM, bo przy InnoDB nie ma to tak wielkiego znaczenia.

Do tego możesz znaleźć optymalną długość pola search_txt:

Patrzysz na maksymalną długość tekstu w kolumnie i na jej podstawie (+ mały zapas) określasz najbardziej odpowiednią wielkość pola.

Długość indeksu najwygodniej, gdy zapełnia całą przestrzeń pola. Nigdy nie oszczędzaj dysku kosztem zużycia procesora. Wychodzi dużo taniej.