At 11:59 +1100 3/20/02, Kim Kohen wrote:
>Hi All,
>
>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:
MATCH(Writer,Heading,Section,Publication) AGAINST('param')
>
>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)
>
>kim
>
>mysql, query, sql
---------------------------------------------------------------------
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
