[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