MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Steven Roussey Date:September 17 2003 7:02pm
Subject:Re: Suboptimal index-usage with FULLTEXT-search
View as plain text  
> > The WHERE-clause for these searches sometimes is a combination 
> > of different columns in the table, and I have noticed some 
> > very sub-optimal index-usage
> Adding correct estimations for MATCH is in todo.
> Until it's done you can use USE_INDEX/IGNORE_INDEX as a simple 
> workaround.

FORCE_INDEX works in this case. I don't think USE_INDEX does since the FTS
index always reports one match and that overrides the USE_INDEX hint.

However, at this point the FTS index is not used at all. If the original
poster was always doing a query where the other columns narrowed down the
matches significantly, then it would be decided to never really use the full
text index.

Say in the fictional case of a table of messages that belong to a million
different forums. When searching inside one forum, the fulltext index would
likely always have more matches than the key on the forumid.

This won't really get fixed unless you can do a composite index of normal
and fulltext indexes.

Even with tsearch2 which is coming in Postgres 7.4, it doesn't let you do a
composite index. :( It does have some nice language configuration stuff (for
example, you can tell it that it is to be parsed as English and it will then
send it to an English stemmer and an English stopword list -- I don't know
if you can preprocess it ).

There is a very ugly workaround on this. You could fake a composite index.
Assuming that you create a separate copied table for FTS, in that table scan
all the words in the document and prefix them with the other column. First
drop all the one and two character words (and the stopwords) yourself. Then
insert something like this as the message "f31334wanted f31334say
f31334hello" instead of "I wanted to say hello". Do the same with the search
and suddenly things work a lot faster. And uglier...

Steven Roussey 

Suboptimal index-usage with FULLTEXT-searchTobias Lind10 Sep
  • Re: Suboptimal index-usage with FULLTEXT-searchSergei Golubchik10 Sep
Re: Suboptimal index-usage with FULLTEXT-searchSteven Roussey17 Sep