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]