Stuck with a simple query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
mzfp2
Forum Contributor
Posts: 137
Joined: Mon Nov 11, 2002 9:44 am
Location: UK
Contact:

Stuck with a simple query

Post 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 !
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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;
mzfp2
Forum Contributor
Posts: 137
Joined: Mon Nov 11, 2002 9:44 am
Location: UK
Contact:

Post 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?
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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.
mzfp2
Forum Contributor
Posts: 137
Joined: Mon Nov 11, 2002 9:44 am
Location: UK
Contact:

Post 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.
Post Reply