sql query using FULLTEXT index

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
stubarny
Forum Newbie
Posts: 10
Joined: Thu Jul 27, 2006 12:53 pm

sql query using FULLTEXT index

Post by stubarny »

pickle | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too. Also - properly indenting code/sql makes it much easier to read[/color]


Hi eveyone,

I'm using the following code to find search results for a jobs board.
[syntax="sql"]
select * from ( SELECT ad_title, ad_location, ad_number, ad_timestamp, MATCH (ad_title) AGAINST ('+engineer ' IN BOOLEAN MODE) FROM ads_live WHERE ( ( ad_type = 'permanent' ) OR ( ad_type = 'contract' ) OR ( ad_type = 'temporary' ) OR ( ad_type = 'part_time' ) OR ( ad_type = 'internship' ) ) AND ( ad_iso_country = 'us' ) LIMIT 50,100 ) as a order by a.a.ad_timestamp DESC 
The code finds adverts that match the selected criteria, with the exception of the fulltext index search on the ad_title column for the keyword 'engineer' - it is returning all records whether or not they include the keyword.

Do you have any idea why this may be happening and how to ensure only records that include the keyword 'engineer' are returned?

Thanks!

Stu


pickle | Please use[/syntax]

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
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 »

I'm guessing that it's not actually the fulltext section that is returning wrong rows, but rather one of the other clauses is matching & you're not realizing it. Your fulltext syntax looks correct. Try a smaller query with just the fulltext conditions & see if it works.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
stubarny
Forum Newbie
Posts: 10
Joined: Thu Jul 27, 2006 12:53 pm

Post by stubarny »

Code: Select all

SELECT * FROM 

( 
SELECT ad_title, ad_location, ad_number, ad_timestamp, MATCH (ad_title) AGAINST ('+engineer ' IN BOOLEAN MODE) FROM ads_live WHERE 
(
( ad_type = 'permanent' ) OR ( ad_type = 'contract' ) OR ( ad_type = 'temporary' ) OR ( ad_type = 'part_time' ) OR ( ad_type = 'internship' )
)
 AND 
( ad_iso_country = 'us' ) LIMIT 50,100 
) 

AS a ORDER BY a.a.ad_timestamp DESC
Thanks for your help, I think i must be misunderstanding how this code is working. I thought that the code above finds all the records that satisfy the clauses after "WHERE" and then applies the MATCH fulltext search on those records?

The unwanted results I'm getting do satisfy the clauses after "WHERE" so I think you're right. Is there a way to modify the code so that the records are only returned if both the MATCH clause and the clauses after "WHERE" are satisfied?

Thanks :)

Stu[/syntax]
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 »

Since the FULLTEXT match isn't in the where clause, it isn't applied as a condition & therefore has absolutely no effect on the rows returned. The match (which would be a yay or nay) is returned as a field in the result set.

To make the fulltext a condition, move the whole "MATCH... IN BOOLEAN MODE" section into WHERE section:

Code: Select all

SELECT
  ad_title,
  ad_location,
  ad_number,
  ad_timestamp
FROM
  ads_live
WHERE
  ad_type IN('permanent','contract','temporary','part_time','internship') AND
  ad_iso_country = 'us' AND
  MATCH(ad_title) AGAINST ('+engineer ' IN BOOLEAN MODE)
ORDER BY
  ad_timestamp DESC
LIMIT 
  50,100
A few other notes:
- I'm not sure why you're doing a sub-query - it would be simpler to just order your main query (like I've done).
- Using the "IN" syntax is much nicer than multiple OR'd statements
- I haven't tested my/your new query, but I'm pretty sure it'll work.
- The MySQL FULLTEXT documentation does have some notes on moving the MATCH...AGAINST clause into the WHERE clause. You have to look for it a bit though.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Post Reply