Dizajn search baze podataka?

[Peca]

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:

+----+-------------+---------------------+-------+---------------+--------------+---------+------+-------+-------------+
| 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?