[thelist] msql: using an index to search across columns?

[Raditha Dissanayake]

You might want to switch to searches that make use of full text indexes, 
these offer a lot more power kind of like a very simple search engine.

Dunstan Orchard wrote:

>Hi there,
>
>I'm trying to do searches in mysql and I have a quick question:
>
>I have 2 cols in my DB: title and body. When people enter search terms, these
>two cols are examined.
>
>An example query might be:
>
>SELECT DISTINCT title, body FROM blog_post WHERE ((title LIKE '%telegraph%' AND
>title LIKE '%death%') OR (body LIKE '%telegraph%' AND body LIKE '%death%'))
>
>That query checks to see:
>
>[1] If ALL search terms appear in the title, OR
>[2] If ALL search terms appear in the body
>
>What I'd like to be able to do is add a third rule to that:
>
>[3] If ALL search terms appear in the title and body combined (Though I guess
>this would replace the first two really)
>
>Because at the moment posts like this aren't being picked up:
>
>Title = "Death of a gentleman"
>Body = "The Daily Telegraph today reports that Mr X has died..."
>
>because the two terms being searched for ('death' and 'telegraph') are split
>over the title and body.
>
>I guess I could create a group of additional clauses such as:
>
>(title LIKE '%telegraph%' AND body LIKE '%death%') OR (title LIKE '%death%' AND
>body LIKE '%telegraph%')
>
>but never knowing how many search terms people will enter that seems to be a
>daft idea.
>
>
>Can anyone suggest what I should do about this? It seems to me like I should
>create an index and search that, but the only way I know of doing that is by
>doing the Full-text search, and that doesn't give me satisfactory results.
>
>Any suggestions gratefully received.
>
>- Dunstan
>
>---------------------------
>Dorset, England
>http://www.1976design.com/
>http://www.1976design.com/blog/
>  
>


-- 
http://www.radinks.com/upload
Drag and Drop File Uploader.