At 11:59 +1100 3/20/02, Kim Kohen wrote:
>My apologies for asking what may be a very newbie question. I'm having
>trouble getting my head around searching multiple indexed columns. I have
>read the MySQL docs 5.4.3, 5.4.4, 5.4.5 and 6.8 full text and have scoured
>Paul's book but haven't been able to find an answer.
>We have a table of news stories which includes columns for Writer, Heading,
>Caption, Section, Publication and the Story_Content (usually 500-1000
>words). Story_Content is of type 'text' and has a fulltext index - all other
>columns are indexed varchar() types.
>We'd like to be able to write a single select statement which combines a
>MATCH statement for the Story_Content column with a LIKE "%param%"
>statement for the Writer, Heading, Section and Publication columns.
>Is it possible to mix these types of selects in a single statement?
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.
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:
>I'm using OSX, mysql 3.23.39 and a php web interface and I expect the table
>to grow to several hundred thousand rows.
>Many thanks (from a real mysql novice)
>mysql, query, sql