MySQL Full-text search (пак)

Днес ми остана малко време и реших да пробвам нещо, за което се каня отдавна.
Постановката:
1. MySQL 4.0.5beta;
2. Таблица Table1 с полета f1, f2, f3, f4 (varchar), f5 (longtext);
3. Fulltext-индекс по петте полета;
4. Малко над 100 000 записа.

Експериментът:
Да се провери разликата в бързодействието при работа с индекса и без него - идеята е да не го ползвам в редките случаи, когато в приложението имам търсене само в едно от полетата.
1.1. SELECT * from Table1 WHERE MATCH(f1, f2, f3, f4, f5) AGAINST('тестстринг') -> ~25 sec. ("тестстринг" се среща доста из текстовете)
1.2. EXPLAIN SELECT... -> Table1,fulltext,ft,ft,0,,1,Using where
2.1 SELECT * FROM Table1 WHERE POSITION('тестстринг' IN f1)>0 OR POSITION('тестстринг' IN f2)>0 ... OR POSITION('тестстринг' IN f5)>0 -> ~13 sec.
2.2. EXPLAIN SELECT... -> Table1,ALL,,,,,100043,Using where

Как би трябвало да се тълкува това? Аз ли пак нещо съм проспал, версията ли е виновна (да, да, знам, че е отдавна неактуална :-) )...?

p.s. Двете заявки наистина не са съвсем еквивалентни (първата търси цели думи, втората - произволни поднизове) - за приложението това не е от значение.

Би трябвало с LIKE "%тестстринг%" да е още по-бързо от POSITION()

Всякак може да се тълкува.
Межа да е от версията (много промени има от тогава), може просто в твоя случай индекса да си е по-бавен и това си е.

salle.online.bg/sig

С LIKE наистина става по-бързо, но разликата при мен е пренебрежимо малка.
Междувременно продължих да се бъзикам и установих, че колкото по-рядко се среща една дума, толкова по-ефективно е търсенето с индекса. Търсене на дума, фигурираща в около 100 записа, минава за около 2 секунди срещу около 12 за заявка с POSITION(). И въобще, на POSITION и LIKE не им пука особено колко често се среща търсеният низ - те така и така си обхождат всичко и разликата във времето е минимална.
По някое време ще пробвам как е ситуацията с повече от една дума.

Ами точно така е и даже ми се струва, че е документирано това за разпределението на думите.

salle.online.bg/sig

да, както каза сале, документирано е и дори е валидно не само за MySQL

почти всяка база го прави - ако дадена заявка ще се обръща към индекса повече от ~30% от редовете в таблицата, то тогава се прави full scan

не съм гледал как MySQL прави търсенето, но бях забелязал в документацията нещо, което обяснява защо 4-ката е по-бърза при повечето like операции - от версия 4 нагоре използват турбо версията на Boyer-Moore алгоритъма за бързо търсене на patterns в стрингове (а тя е със сложност O(N), в най-лошия случай 2n сравнения) - за справка един search google за Boyer-Moore ще ти изкара достатъчно материали

"почти всяка база го прави - ако дадена заявка ще се обръща към индекса повече от ~30% от редовете в таблицата, то тогава се прави full scan"

Де да беше така

Работата е, че подобна оценка за Full-text индексите не може да се направи. Т.е. ако се използва MATCH оптимизатора ВИНАГИ използва Full-text индекса. Което както AcidMemory казва не винаги е добра идея.

И точно тук е обяснението на ефекта, който Bass е забелязал.

За думи които се срещат многократно и за които би било по-добре да се направи пълно търсене вместо по индекс Оптимизаторът няма начин да забележи, че случая е такъв.

salle.online.bg/sig

>>Работата е, че подобна оценка за Full-text индексите не може да се направи. Т.е. ако се използва MATCH оптимизатора ВИНАГИ използва Full-text индекса.
Именно. Дори следната заявка за търсене на дума в само 2 записа минава за няколко десетки секунди:
select * from articles where id between 100 and 101 and match(f1,f2,f3,f4,f5) against('тестстринг')
Обаче, ако вместо "id between 100 and 101" сложим само "id=100", вече не се ползва FullText-a :-))
Както и да е, всичко е въпрос на компромиси и освен чисто формалните начини за оценка на производителността (EXPLAIN, структура на индексите, etc.) понякога просто си трябва емпиричен подход и нагаждане :-\\

Logichno imash "id=100" , koeto naj-weroqtno e PRIMARY KEY :)

много готино нещо са пълнотекстовите индекси и търсене, аз ползвам обаче REGEXP :)

... предимството на индекса нараства значително. При търсене на същата честосрещаща се дума плюс още една случайно избрана с индекса става около 2 пъти по-бързо. С 3 думи - около 6 пъти по-бързо.

не е точно така ...

точно тук трябва да влезе в играта Cost-Based Optimizer-а
при наличие на статистика и "доброто и интерпретиране" би трябвало да се избере по-правилния път

REGEXP пък по никакъв начин не може да използва никакъв индекс

Щом използваш REGEXP за какво изобщо ти база данни? Направо дръж нещата в текстов файл и използвай grep или каквото предпочиташ с REGEXP

salle.online.bg/sig

Ideqtq ti nikak ne e losha :)