> > 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... Sincerely, Steven Roussey http://Network54.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
