Oh well...
To sto pisem ovde znaci da sam bas zapao u corsokak
Imao sam 'search' na sajtu koji je do sada sa MyISAM tabelama funkcionisao zadovoljavajuce [ili pak ja nisam primetio da se zapucava]
Problem je nastao kada sam sve tabele konvertovao u InnoDB
No, hajde da se vratimo na pocetak - dizajn tabela:
Imamo standardno dve tabele - words-index i positions-of-words.
words-index tabela:
WordID - naravno unique key
Word - unique polje [dakle isto index]
positions-of-words tabela:
ArticleID - index
WordID - index
kada se radi pretraga - pokupim WordID-eve od datih reci, i onda ide query:
SELECT ArticleID, WordID, COUNT(WordID) AS pogodaka FROM positions-of-words WHERE WordID IN (... spisak WordID-eva...) GROUP BY ArticleID HAVING pogodaka>=4 ORDER by ArticleID DESC LIMIT 0, 12;
ova cetvorka gore je broj reci [varira od broja WordID-eva].
sta query radi:
- nadje sve ArticleID-eve koji sadrze navedene reci (WordID IN (...))
- grupise ih po ArticleID (GROUP BY ArticleID) - ovo nazivam 'ukrštanje'
- generise polje 'pogodaka' koje oznacava koliko je zadatih reci nadjeno u datom zajednickom artiklu (COUNT(WordID) AS pogodaka) - odnosno u ukrstenoj grupi odozgo
- odstranjuje sa liste artikle koji ne sadrze sve reci pretrage (HAVING pogodaka>=)
sve ovo lepo radi dok neko ne pretrazi rec (WordID) koja se u nalazi u 100.000 artikla - jer to znaci da je i u 'positions-of-words' tabeli ima u 100.000 row-a.
I tu stvar zapne - po minut-dva mu treba da pohvata ceo index za datu rec. Jos ako pretrazi dve-tri takve reci u istom upitu - eto ludila.
Evidentan problem ovde je taj sto
on mora sav index artikla za datu rec da fetchuje - da bi ih ukrstio sa indexom artikla druge reci [trece, cetvrte...] - da bi mogao da nadje zajednicke artikle...
To je ogromna kolicina podataka.
EXPLAIN daje sledece:
Kôd:
+----+-------------+---------------------+-------+---------------+--------------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------------+-------+---------------+--------------+---------+------+-------+-------------+
| 1 | SIMPLE | positions-of-words | index | WordID | ArticleID | 4 | NULL | 57691 | Using where |
+----+-------------+---------------------+-------+---------------+--------------+---------+------+-------+-------------+
A nemam ideju kako da ih 'ukrstim' a da ne fetchujem sav index za date reci... to je zapravo i nemoguce pri ovom dizajnu tabela.
InnoDB je naravno zahtevao da se doda UNIQUE ID KEY u tabeli 'positions-of-words'. Dodao sam, to je ubrzalo stvari malo, bar za manje koriscene reci, ali kod mnogo koriscenih reci i dalje izvrsava upit po par minuta.
Buffer pool size je naravno setovan na 70% RAM-a, i ceo index je u RAM memoriji [Buffer pool hit rate 1000 / 1000].
I dalje mi nije jasno zasto se sa MyISAM tabelom ovo sve izvrsavalo za svega par sekundi.
Moze li neko bar da mi da neku smernicu - u kom pravcu da kopam.
Ako je query los - postoji li neki drugi nacin da 'preklopim' indexe od svake reci u search frazi... ili je ceo koncept los?