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.