At 13:28 +1100 3/20/02, Kim Kohen wrote:
>Thank you for your response.
>> Sure, but no index will be used for the LIKE pattern match part of the
>> query because your pattern doesn't begin with a literal string.
>I realised after I sent the previous post that I should have omitted the
>first '%'. I had mentally noted this from what I had read.
>> However, if "param" is a word, you might wanna create a FULLTEXT index
>> on Writer, Heading, Section, Publication. Then use the MATCH() against
>> Story_Content and AND it together with:
>> MATCH(Writer,Heading,Section,Publication) AGAINST('param')
>My apologies, I can see I've been a little ambiguous. Each of these items
>will be a standalone parameter passed from a web form. Therefore I have a
>separate form field (and parameter) for Writer, Publication, Section etc
>where people can enter search criteria.
>We're keen to have '%' wildcard for those searches because the 'viewers'
>might not know complete writer names etc. Also, it makes more sense if a
>search for 'fire' in a heading also finds 'fires'.
>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.
>Cheers and thanks again