W moim filtrze antyspamowym mam filtr bayesowskopodobny, który nauczył się już 4 milionów szeroko pojętych “słów”.

Ponieważ baza ma strukturę (char(32) primary key, int, int), trzymanie w niej indeksu osobno, jak to robi MyISAM, jest bez sensu — lwia część bazy jest dublowana. Natomiast InnoDB po prostu sortuje rzędy wg klucza głównego, więc w tym przypadku nie potrzebuje zapisać ani bitu więcej, niż same dane.

Postanowiłem przestawić bazę na InnoDB prostym

alter table engine=InnoDB

Ups. Zapytanie trwało 5 godzin mielenia dyskiem.

char() w MyISAM miał ważną przewagę nad varchar() — zachowywał stałą wielkość rzędu, co przyśpiesza wyszukwiania. Tylko taki mały problem — stała wielkość to maksymalna wielkość. Dla MySQLowego “UTF—7.5” to po 3 bajty na każdy znak. Każde 32—znakowe słowo zajmuje 97 bajtów.

W przypływie zdrowego rozsądku postanowiłem zapisywać słowa jako hashe MD5. Do wyszukiwania wystarczy, a każde, dowolnie długie słowo, będzie zajmowało 16 bajtów (16 binarnie, 32 dla ASCII-hex-cieniasów). Teoretycznie możliwymi powtórkami nie przejmuję się, bo jak znajdę kolizję na krótkich, czytelnych słowach, to będę sławny i bogaty.

Zmiana klucza głównego na tabeli InnoDB to nie jest dobry pomysł. Dodanie kolumny na hash trwało 20 minut. Potem zorientowałem się, że char(16) binary to zupełnie co innego, niż binary(16) i katowałem bazę przez następne 20 minut tylko po to, żeby odpuścić i (po 20 kolejnych minutach) zmienić strategię na skopiowanie danych do nowej tabeli.

insert into nowatabela select unhex(md5(slowo)), itd, itp from staratabela;

Nie wiem jak długo trwało to zapytanie, bo po 18 godzinach pracy non-stop zlitowałem się nad bazą i je ubiłem.

*) no dobra, nie każdego, bo ma różne optymalizacje z buforami, stronami, itp. ale przy takiej ilości danych najwidoczniej to na niewiele się zdaje.

Diabeł tkwi w szczególe: InnoDB musi mieć zawsze posortowane rzędy. Nowo wstawiane zahashowane MD5 są idealną odwrotnością posortowania, czyli przy wstawianiu każdego* rzędu baza musi przesunąć (statystycznie) połowę już wstawionych. Dla 4 milionów rzędów daje ok. 4 bilionów (amerykańskich trylionów — oni mają wszystko większe) operacji.

I jeszcze bardziej diabelski szczegół:

insert into nowatabela select unhex(md5(slowo)) as hash, itd, itp from staratabela order by hash;

18 minut.