List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:March 20 2002 1:46am
Subject:Re: Searching on multiple indexed columns
View as plain text  
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

Thread
MYSQL DATABASE BACKUPKathy Reyes19 Mar
  • Access denied for userKathy Reyes19 Mar
  • Re: MYSQL DATABASE BACKUPLars Heidieker19 Mar
    • Searching on multiple indexed columnsKim Kohen20 Mar
    • Searching on multiple indexed columnsKim Kohen20 Mar
      • Re: Searching on multiple indexed columnsPaul DuBois20 Mar
        • Re: Searching on multiple indexed columnsKim Kohen20 Mar
          • Re: Searching on multiple indexed columnsPaul DuBois20 Mar
            • Re: Searching on multiple indexed columnsArjen Lentz20 Mar
              • Re: Searching on multiple indexed columnsKim Kohen21 Mar
                • Re: Searching on multiple indexed columnsArjen Lentz21 Mar
RE: MYSQL DATABASE BACKUPBarry L. Jeung19 Mar
RE: MYSQL DATABASE BACKUPSimon Green20 Mar