Page 1 of 1

Stuck with a simple query

Posted: Thu Feb 02, 2006 5:14 pm
by mzfp2
Hi,

I have a table links, which the user can search against using keywords.

The records can be searched against Title and Description which are fields that belong to eachh record.

I am trying to construct a query similar to below

SELECT * FROM links
WHERE Description LIKE '%Keyword1%'
LIMIT 1000;

but that allows me to search for several keywords, aggainst two columns ... ie :

SELECT * FROM links
WHERE Title or Description LIKE '%Keyword1%' OR '%Keyword2%'
LIMIT 1000;

So the Query would search against both Title and Description and search foor one or more keywords.

Hope someone can help !

Posted: Thu Feb 02, 2006 5:19 pm
by pickle
Using your methodology, this is what you need to do

Code: Select all

SELECT
   *
FROM
   links
WHERE
   Title LIKE '%Keyword1%' OR
   Title LIKE '%Keyword2%' OR
   Description LIKE '%Keyword1%' OR
   Description LIKE '%Keyword2%'
However, I'd strongly suggest using a FULLTEXT search - search functionality built in to MySQL. This lets you match against any number of keywords, and you can get relevance returned as well, rather than just returning everything that matches.

MySQL FULLTEXT searching

Posted: Thu Feb 02, 2006 6:40 pm
by raghavan20
It is better if you have full text index but if you are not interested you can use boolean text mode which is of higher clarity than where statements..

Code: Select all

select * from `links`
match (`Title`, `Description`)
against ("keyword1 keyword2" in boolean mode)
limit 1, 1000;

Posted: Fri Feb 03, 2006 5:09 am
by mzfp2
Many thanks for your help, the full-text search seems great, and also solves another problem which I was manually going to implement which is relevancy.

My only question is, will the query be slow on say 20,000+records?

Posted: Fri Feb 03, 2006 5:41 am
by raghavan20
They are two full text searches available one with fulltext index and the other one with or without fulltext index
You have to create full text indexes on the search fields...
example:

Code: Select all

alter table `links`
add fulltext LinkDescriptionIndex (`link`, `description`);
Putting an index would definitely speedup queries on huge volume of records.

Posted: Fri Feb 03, 2006 6:00 am
by mzfp2
Hi,

I have attemprted to use the full-text query as follows :

SELECT * FROM links
WHERE MATCH (Title,Description) AGAINST ('dating')
LIMIT 100;

However for most cases it returnes no rows .. even when the word 'dating' appears in many Descriptions.

I'm not sure how the query is behaving, but it seems it is trying to match both Title AND Description against the keyword, however, is it possible to match Title OR Descrption? .. so the keyword does not have to appear in both columns it is searching against?

I hope the above all makes sense.