Hi all,
I'm planning to use MySQL's full-text search for my forum system
(possibly 5+ million posts). I've been playing with it a lot lately to
see the performance and functionality and have some
suggestions/questions.
First, since a few of you may be wanting to know, here is a thread where
I was doing some speed/optimization tests and stuff with 3 million
posts: http://www.sitepointforums.com/showt...threadid=69555
(From post #12)
Especially discovered that IN BOOLEAN MODE is really slow if you want to
sort by relevance (with a lot of matching rows anyway). :-( For
non-BOOLEAN searches, though, I can get 1000 relevance-sorted results in
about 8-10 secs. for searches that match a LOT of rows and everything
has to be read from disk. The full-text processing seems to be very fast
(max 1-2 seconds of "FULLTEXT initialization" in PROCESSLIST). It's the
disk seeks to read random rows from the data file ("Sending data") that
take the most time (7200 RPM/~8ms seek IDE drive). Searches are *MUCH*
faster when the needed parts of the data file are cached by the OS!
Anyway, my suggestions:
--------------------------------------------------
*) Min/Max Word Length -- This should really be able to be set on at
least a per table basis (others may want per index). Right now, people
that don't have control of the server are at the mercy of the admin to
change the min/max word length.
I would also suggest that ft_min_word_len be 3 and ft_max_word_len be 32
by default. I think these would be better defaults for everyone than the
current 4/254.
Or if we could use
SET ft_min_word_len=n;
etc. for the current connection it would be nice.
*) Parser: Indexing of Any and All Numbers -- I think it would be a good
idea to index any sequence of digits less than ft_min_word_len long.
Anything numeric could be very relevant for searching -- software
versions, ages, dates, etc. -- and shouldn't be excluded.
Even anything *containing* a number (among letters) is probably relevant
for searching, again, even if it's shorter than ft_min_word_len. e.g.
RC1, B2, 8oz, F5, etc.
*) Parser: Other Things -- I've seen people trying to search
catalog/item/part numbers with "pieces" of the "number" separated by -
or / for example (making some "pieces" too short). How about indexing
words that are on either side of a "-" or "/" (with no space) no matter
their length? I don't mean including the - or / in the index -- just the
usual word characters on either side (I think) as *separate* words, not
a *single* word with the - or / removed. This would help with things
like CD-ROM, TCP/IP, etc.
Single quotes being counted as a word character is another issue I have.
(I discovered that they're not counted as part of the word when on the
end(s): 'quote' (thank God! :-))) Example: if someone searches for
MySQL, it won't find rows with MySQL's. Since possessive's (sic) are the
biggest problem, how about stripping any 's from the end of the word in
the index? So MySQL's would be indexed as MySQL.
*) "Always Index" Words -- Like it says in the full-text TODO section of
the manual. This should be able to be set on at least a per table basis
(again, others may want per index).
*) Stopword File -- I would also like to be able to define this per
table somehow.
*) Miscellaneous -- Mostly functionality related, from the TODO:
STEMMING! (controlled more finely than server level I hope), multi-byte
character set support, proximity operators. Anything to get it closer to
Verity's full-text functionality. ;-)
Any speed/optimization improvements are welcome for gigs of data,
especially with IN BOOLEAN MODE (e.g. automagically sorted by relevance
like a natural language query, although this is probably difficult if a
wildcard* is used?). And the FULLTEXT index shouldn't always be chosen
for non-const join types when another index would find less rows first.
e.g. ... WHERE MATCH ... AND primary_key IN (1, 2); should use the
PRIMARY key, not the FULLTEXT. :-) But maybe that's not possible, since
I guess it's a problem auto sorting by relevance if it's not using the
FULLTEXT index.
--------------------------------------------------
To other full-text users: what do you think of these suggestions?
To the developers: any word on if and when any of these things would be
implemented? I know from the TODO and other list messages that some
will. Any *estimates* (in months or MySQL version) on when would be
great. Just any info on new full-text features, even ones that I didn't
mention, would be awesome to hear. :-) And like how they would be
implemented and used by us (syntax, etc.).
How about changing the default min/max (or just min if you want) word
length? I think everyone *really* wishes ft_min_word_len was 3. Seems
like that and indexing numbers shorter than min_word_len could be easily
done. Please? :-)
Here's a couple mailing list threads about full-text:
http://lists.mysql.com/list.php?3:sss:2365
http://lists.mysql.com/list.php?3:sss:6749
There Sergei is talking about a new .frm format (plain text) that will
allow more of these features. Will it allow us to somehow define how to
parse things or something?? Could you elaborate more on what this will
bring? In November 2001, he said the new .frm format would be here "this
year." It's been almost 2 years since then, so when is it do? ;-/ Talk
of a "dynamic" stopword list sounds interesting.
Also, are the current MySQL versions using the "2 level" full-text index
format yet? I'm thinking not?
Finally, in the full-text TODO, it says "Generic user-suppliable UDF
preparser." Could you also elaborate on this? The "generic" part almost
makes it sound like some sort of "script" to define how to parse the
text. But UDF makes it sound like a separate thing that has to be loaded
with CREATE FUNCTION. But UDFs won't work with your MySQL binaries, will
they, since they're complied statically?
Looking forward to any comments from the developers and other users.
Thanks in advance!
Matt
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/my***********...ie.nctu.edu.tw
