Dan Salzer <[EMAIL PROTECTED]> wrote on 16/05/2005 14:36:41:
> I have the following table:
>
> > CREATE TABLE `Article_Search` (
> > `ArticleID` int(11) NOT NULL default '0',
> > `Content` text NOT NULL,
> > PRIMARY KEY (`ArticleID`),
> > FULLTEXT KEY `Content` (`Content`)
> > ) ENGINE=MyISAM DEFAULT CHARSET=latin1
>
> This table has several million rows, but I only want to search a subset
of
> the table. IE:
> SELECT * FROM Article_Search WHERE MATCH(Content) AGAINST('"rubber
> duckies"' IN BOOLEAN MODE) AND ArticleID IN (100, 23, 223, 98, 4018,
1452,
> 91)
> The reason I'm specifying a set of ArticleIDs is that I know any hits
are
> going to be within those articles. So the presence of the IN() clause is
> purely there for performance. However, an explain on this Statement
shows
> that it is using the Full-Text index. Is mysql text-searching the entire
> table under the hood, or does it use the PK to reduce the dataset before
the
> text-search.
MySQL can only use one index at a time. So if it used the ArticleID index
and your IN clkause as the primary index, it would be reduced to doing the
MATCH() the hard way, line by line, in the articles returned by the IN
clause.
On the other hand, you know that the only articles which contain the words
that you specify, it will be doiing a relatively fast lookup in the
FULLTEXT index to get the same set of IDs that you are feeding it, or an
even smaller one (because some even of those will not contained in the
hits). the only case where the simply doing the FUULTEXT search would not
be as fast as you quote would be when one of the separate words "rubber"
or "duckies" has a very large number of hits but the phrase does not.
In sum, I wouldn't bother with this optimisation unless your search truens
out in practice to be slow.
Alec
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
