Hi.
I thought it would be useful to share my findings. They all relate to the
4.0.1 release. It would be nice to have some clarification on whether this is
expected behaviour, whether this behaviour is wrong (i.e. bug, corrupted
index, etc), and what you guys think could be causing it.
1.1) Full Text Search can, according to the manual, be performed without the
FTS index, but it is slower.
1.2) MySQL can only use 1 index per join per table.
=> This means that if I specify the USE INDEX (some_non_fulltext_index), the
FTS will be performed without the index, and this will only work IN BOOLEAN
MODE. Otherwise, MySQL returns an error, complaining about the lack of an
index.
HOWEVER, please someone explain why the following results are happening:
Two nearly identical queries, similar to:
SELECT Table1.ID,
Table1.Title,
Table1.Type,
Table1.Description,
DATE_FORMAT(Retrieved, '%d-%b-%Y %H:%i:%S') AS Retrieved
FROM Table1
WHERE Type = 'SomeType'
AND Retrieved > '20020118000000'
MATCH (Title, Description) AGAINST ('some words to match' IN
BOOLEAN MODE)
ORDER BY MATCH (Title, Description) AGAINST ('some words to match' IN
BOOLEAN MODE) DESC;
and
SELECT Table1.ID,
Table1.Title,
Table1.Type,
Table1.Description,
DATE_FORMAT(Retrieved, '%d-%b-%Y %H:%i:%S') AS Retrieved
FROM Table1 USE INDEX (Table1_Retrieved_Index)
WHERE Type = 'SomeType'
AND Retrieved > '20020118000000'
MATCH (Title, Description) AGAINST ('some words to match' IN
BOOLEAN MODE)
ORDER BY Retrieved DESC;
These two queries return DIFFERENT numbers of records!
If my understanding of the documentation is correct, the second example
should be slower because the FTS index isn't used. But the results should be
the same right? Well, that definitely isn't the case in my database.
I have just done a REPAIR TABLE Table1, Table2... EXTENDED, so the tables
definitely aren't corrupted.
The FTS index search returns 24 records on my data set (~ 60K records), and
the non-fts search returns 7 records.
The reason I have been even trying this is because FTS is a bit slow for some
of the things I am doing. By limiting the data set through the "Retrieved"
date field, I can usually cut the data down to about 10% of the total size,
hoping that non-indexed FTS on that will be faster.
Well, it turned out to be faster for cases where the data set was cut down a
lot by the index, but the IN BOOLEAN MODE FTS doesn't seem to be reacting to
things like '-word' in the MATCH/AGAINST clause, as it should per the FTS
search. Sometimes, specifying a '-word' that should only remove a few results
returns 0 rows - which is clearly wrong in some cases.
Is there a know bug in the indexless FTS that causes this? The indexed FTS is
behaving well, but I was really hoping to gain some speed by using a
different index in some specific cases...
Regards.
Gordan
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
