Optymalizacja bazy danych (szybkość) Opcje

[husky83]

Mam pewien problem.. Mam bazę danych MySQL, w której (obecnie) jest niecałe 2mln rekordów.
Jest to dosyć duża tablica, ma łącznie ponad 30 pól (kolumn?), wg. phpmyadmin jeden rekord zajmuje średnio 165b (czyli nie tak dużo).

Problem jest taki - obecnie znalezienie kilku rzeczy w tej tabeli zajmuje kilka sekund, np. trzy rzeczy (`tablica` LIKE '%klucz%' OR `tablica` LIKE '%klucz1% ' OR`tablica` LIKE '%klucz2%') to ponad 5 sekund... Niby nic strasznego, ale docelowo baza ma mieć kilkanaście milionów rekordów, wtedy ten czas wydłuży się kilkukrotnie..

W jaki sposób można to zoptymalizować?

Czy zrobić dodatkową tabelę, zawierającą wyłącznie słowa kluczowe (czyli rozdzielone po spacji, przecinkach itd to, co znajduje się obecnie w `tablica` + do tego numer ID z tej 'dużej' tabeli?)?
Da mi to tyle, że nie będę musiał używać LIKE %klucz%, bo będzie tylko jedno konkretne słowo (gorzej z całymi wyrażeniami, ale to też da się zrobić) i będę miał ID już konkretnych rekordów, które sobie potem spokojnie pobiorę...

A może wystarczy wydzielić do osobnej tabeli te dane, wg których przeszukuje i łączyć je przez ID?

Czy to przyspieszy sprawę?

Nie mam pomysłu, a niestety, kiedy baza się rozrośnie (a to następuje bardzo szybko) to czasy wyszukiwania pewnie bardzo się wydłużą...


Cała baza leży na VPS'ie, więc mogę w pełni konfigurować cały serwer - może optymalizację można zrobić także w plikach konfiguracyjnych?

A może przenieść się na inną bazę? (bezpłatną)

Z góry dziękuję za wszelką pomoc (IMG:style_emoticons/default/smile.gif)

Ten post edytował husky83 4.08.2011, 06:10:23

[thek]

Jeśli masz silnik to pozwalający zrobić.... FULLTEXT search. System tagów typu n-n (wiele do wielu) to także rozsądny pomysł, choć dla wyrażeń dłuzszych nieco mniej dobry, gdyż tagi też musiały by być wielowyrazowe lub bedziesz zmuszony do kombinowania strasznego.

[uupah5]

Mam pewien problem.. Mam bazę danych MySQL, w której (obecnie) jest niecałe 2mln rekordów.
Problem jest taki - obecnie znalezienie kilku rzeczy w tej tabeli zajmuje kilka sekund, np. trzy rzeczy (`tablica` LIKE '%klucz%' OR `tablica` LIKE '%klucz1% ' OR`tablica` LIKE '%klucz2%') to ponad 5 sekund...

1. nie ma sensu przechodzić na inną bazę, szczególnie, jeśli znasz je gorzej niż mysql
2. 2mln rekordów to śmieszna ilość, baza nie ma prawa mulić (patrz 3)
3. normalizacja, indeksacja!
4. LIKE '%string%' jest jedną z mniej wydajnych kwarend, albo możesz ograniczyć do LIKE 'string%' albo tutaj szukaj możliwości optymalizacji:
a ) wydzielenie tabeli myisam na serche + wyszukiwanie pełnotekstowe
b ) preindeksacja i wyszukiwanie po preparowanych wynikach
c ) lucyna, sfinks, solr, ...

Ten post edytował uupah5 4.08.2011, 07:59:03

[husky83]

Zrobiłem tablicę (z polami, które przeszukuję) MyISAM z wyszukiwaniem pełnotekstowym (póki co dla jednej z kolumn) ... Działa błyskawicznie, ale.... Jednak jest pewien problem...

[SQL] pobierz, plaintext 
SELECT * 
FROM  `stats_index` 
WHERE  `end` =0 AND (    (MATCH (`tabela`) AGAINST ('test1')) OR (MATCH (`tabela`)AGAINST ('test2')) )
[SQL] pobierz, plaintext 

daje mi jakieś 6000 wyników, tymczasem

[SQL] pobierz, plaintext 
SELECT * 
FROM  `stats_index` 
WHERE  `end` =0 AND (  `tabela` LIKE '%test1%'  OR `tabela` LIKE '%test2%' )
[SQL] pobierz, plaintext 

daje już 15000 wyników (IMG:style_emoticons/default/exclamation.gif)

Przeglądając listę wyników zwykłego wyszukiwania widzę, że wszystkie wyniki są ok, to znaczy, że wyszukiwanie MATCH...AGAINST nie daje pełnych wyników (albo po prostu muszę poczytać dokładniej co i jak ;P)


EDIT (IMG:style_emoticons/default/smile.gif)
Awaria myślenia (IMG:style_emoticons/default/smile.gif)

LIKE %test%
daje w wynikach także np: 'tester','testowanie', a match sprawdza (chyba) całe wyrazy i znajduje tylko 'test'!
Ot takie małe info dla tych, którzy by też się męczyli z wyszukiwaniem (IMG:style_emoticons/default/smile.gif)

Ten post edytował husky83 4.08.2011, 09:22:43

[thek]

MATCH AGAINST ma pewne ograniczenia wynikające z jej wewnętrznej składni i optymalizacji
http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html
Zwróć uwagę zwłaszcza na różnicę między różnymi rodzajami full-text searcha (też są pewne limity opisane), ograniczeniami i "podkręcaniem" tego indexu.

[husky83]

Baza rozrosła mi się troszkę, obecnie jest ok. 20 mln wpisów (ok. 5GB)... I problem w tym, że zwykłe zapytanie z FULL-TEXT też zaczyna trwać dosyć długo...
Obecnie tak proste wyszukiwanie jak poniższe może trwać nawet kilka minut (zazwyczaj 1 - 3 minuty).

[SQL] pobierz, plaintext 
SELECT * FROM `tablica` WHERE  MATCH (`nazwa`) AGAINST ('wyrażenie')
[SQL] pobierz, plaintext 

Niestety biorąc pod uwagę, że takich zapytań muszę wywołać kilka podczas pracy skryptu, czas generowania strony staje się nie do przyjęcia (IMG:style_emoticons/default/sad.gif)

Znalazłem jakieś informacje o wyszukiwarce Sphinx. Czy zaprzęgnięcie jej tutaj ma sens?

Ten post edytował husky83 22.08.2011, 18:12:14