Nie rozumiem tego.

Mam tabelę z tymi indeksami

PRIMARY     post_id
INDEX       topic_id
FULLTEXT    post_text

Tabela ma (tylko) 346 000 wierszy. Próbuję wykonać 2 zapytania.

SELECT post_id 
FROM phpbb_posts 
WHERE topic_id = 144017 
AND post_id != 155352 
AND MATCH(post_text) AGAINST('http://rapidshare.com/files/5494794/photo.rar')

trwa 4,05 sekundy

SELECT post_id 
FROM phpbb_posts 
WHERE topic_id=144017 
AND post_id != 155352 
AND post_text LIKE ('%http://rapidshare.com/files/5494794/photo.rar%')

zajmuje 0,027 sekundy.

EXPLAIN pokazuje, że jedyną różnicą są możliwe fulltextklucze ( zawiera tekst post_tekst, LIKEnie ma)

To naprawdę dziwne.

Co za tym stoi? Co dzieje się w tle? Jak może LIKEbyć tak szybko, gdy nie używa się indeksu, a FULLTEXT tak wolno, gdy używa się jego indeksu?

AKTUALIZACJA 1:

Właściwie zajmuje to teraz około 0,5 sekundy, być może stół został zablokowany, ale mimo to po włączeniu profilowania pokazuje, że INICJALIZACJA PEŁNOTEKSTOWA trwała 0,2 sekundy. Co tam?

Mogę przeszukiwać tabelę LIKE10 razy na sekundę, a pełny tekst tylko 2x

AKTUALIZACJA 2:

Niespodzianka!

mysql> SELECT post_id FROM phpbb_posts WHERE post_id != 2 AND topic_id = 6 AND MATCH(post_text) AGAINST ('rapidshare.com');
Empty set (0.04 sec)

więc pytam, jak to możliwe?

Dodatkowo,

SELECT count(*) FROM phpbb_posts WHERE MATCH(post_text) AGAINST ('rapidshare.com')

jest naprawdę wolny. Czy pełny tekst może być uszkodzony?

AKTUALIZACJA 3:

Co do cholery?

SELECT forum_id, post_id, topic_id, post_text  FROM phpbb_posts  WHERE MATCH(post_text) AGAINST ('rapidshare.com') LIMIT 0, 30;

trwa 0,27 sek

SELECT count(*) FROM phpbb_posts  WHERE MATCH(post_text) AGAINST ('rapidshare.com') LIMIT 0, 30;

zajmuje ponad 30 sekund! Co tu idzie nie tak?

Myślę, że problem może wynikać z obecności samego indeksu FULLTEXT.

Za każdym razem, gdy występuje zapytanie dotyczące indeksu FULLTEXT, MySQL Query Optimizer dąży do rozbicia zapytania na pełny skan tabeli. Widziałem to przez lata. Napisałem też wcześniejszy post na temat tego najbardziej błahego zachowania w indeksach FULLTEXT .

Może być konieczne zrobienie dwóch rzeczy:

1. refaktoryzuj zapytanie, aby indeks FULLTEXT nie powodował dezorientacji Optymalizatora zapytań MySQL
2. Dodaj dodatkowy indeks, który będzie poprawnie obsługiwał refaktoryzowane zapytanie

REFAKTOR ZAPYTANIA

Oto twoje oryginalne zapytanie

SELECT post_id  
FROM phpbb_posts  
WHERE topic_id = 144017  
AND post_id != 155352  
AND MATCH(post_text) AGAINST('http://rapidshare.com/files/5494794/photo.rar') 

Konieczne będzie przefiltrowanie zapytania w następujący sposób:

SELECT subqueryA.post_id
FROM
(
    SELECT post_id FROM phpbb_posts
    WHERE topic_id = 144017
    AND post_id != 155352
) subqueryA
INNER JOIN
(
    SELECT post_id FROM phpbb_posts
    WHERE MATCH(post_text) AGAINST('http://rapidshare.com/files/5494794/photo.rar')
) subqueryB
USING (post_id);

STWÓRZ NOWY INDEKS

Będziesz potrzebował indeksu do obsługi subqueryA. Masz już indeks na topic_id. Musisz go wymienić w następujący sposób:

ALTER TABLE phpbb_posts ADD INDEX topic_post_ndx (topic_id,post_id);
ALTER TABLE phpbb_posts DROP INDEX topic_id;

Spróbuj !!!

AKTUALIZACJA 2012-03-19 13:08 EDT

Spróbuj tego pierwszego

SELECT post_id FROM
(
    SELECT * FROM phpbb_posts
    WHERE topic_id = 144017
    AND post_id != 155352
) A;

Jeśli działa to szybko i zwraca niewielką liczbę wierszy, spróbuj tego zagnieżdżonego podzapytania:

SELECT post_id FROM
(
    SELECT * FROM phpbb_posts
    WHERE topic_id = 144017
    AND post_id != 155352
) A
WHERE MATCH(post_text) AGAINST('http://rapidshare.com/files/5494794/photo.rar');

AKTUALIZACJA 2012-03-19 13:11 EDT

Porównaj czas działania tego:

SELECT count(*) FROM phpbb_posts  WHERE MATCH(post_text) AGAINST ('rapidshare.com') LIMIT 0, 30;

z tym

SELECT count(*) FROM phpbb_posts WHERE 1 = 1;

Jeśli czas wykonywania jest taki sam, klauzula MATCH jest wykonywana w każdym wierszu. Jak już wspomniałem wcześniej, użycie indeksów FULLTEXT ma tendencję do unieważniania wszelkich korzyści, do których spróbował i wniósł wkład MySQL Query Optimizer.