On Wed, 2002-03-20 at 12:56, Paul DuBois wrote:
> >With Story_Content, we're happy to use the stricter MATCH selects. So is
> >it possible to do something like Select * from stories where
> >MATCH(Story_Content) against ('param1') AND Writer LIKE "param2%" or am I
> >barking up the wrong (b-) tree?
>
> If you're asking whether that's *legal*, the answer is "sure".
> If you're asking whether it's efficient, you probably want to make
> sure that Writer has an index on it so that the LIKE can use it.
Ha. That got my attention and curiosity: I asked Sergei a few questions
about this.... let's see if I can summarise this fully so it can be
documented ;-)
FULLTEXT search generally uses an index (the FULLTEXT index), so the
server will not use an index on Writer.
So far for the answer to Kim.
There are situations where the optimiser will not use a fulltext index:
1) a fulltext search IN BOOLEAN MODE does not *require* the existance
of a FULLTEXT index.
2) if the MATCH is part of an expression.
Case 2 provides a magical exception to the normal rule that the server
will only select at most 1 index per included table in a query. The
optimiser will in this case not pick the MATCH for index use, so it
might pick another field (like Writer). However, *internally*, the
fulltext search function will still use the FULLTEXT index. Therefore,
effectively, two indexes can/will be used! ;-)
Regards,
Arjen.
--
Get MySQL Training Worldwide, http://www.mysql.com/training/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Arjen G. Lentz <[EMAIL PROTECTED]>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer, Trainer
/_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia
<___/ www.mysql.com
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
