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

Dunstan Orchard dunstan at 1976design.com
Mon Sep 29 08:06:23 CDT 2003


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/


More information about the thelist mailing list