6 messages in com.mysql.lists.win32RE: Fulltext search with terms stored...
FromSent OnAttachments
Randy Clamons21 Apr 2005 15:50 
SGr...@unimin.com22 Apr 2005 06:02 
Randy Clamons22 Apr 2005 12:35 
SGr...@unimin.com22 Apr 2005 13:47 
Randy Clamons22 Apr 2005 14:42 
jbon...@sola.com.au25 Apr 2005 15:53 
Subject:RE: Fulltext search with terms stored in table
From:jbon...@sola.com.au (jbon@sola.com.au)
Date:04/25/2005 03:53:38 PM
List:com.mysql.lists.win32

You can easily get around that of course, by running two queries and constructing the second from the results of the first.

John Bonnett

-----Original Message----- From: Randy Clamons [mailto:ran@novaspace.com] Sent: Saturday, 23 April 2005 7:13 AM To: SGr@unimin.com Cc: MySQL Mailing List Subject: Re: Fulltext search with terms stored in table

Apparently AGAINST won't take a variable or a column-name as an argument! :(

Oops! I should have RTFM. Section 6.8.1 Full-text Restrictions--the argument to AGAINST() must be a constant string. (manual.html#Fulltext_Restrictions).

Oh well...It seemed like a good idea at the time.

Randy Clamons Systems Programming Novaspace.com

------------Original Message------------ From: SGr@unimin.com To: ran@novaspace.com Cc: "MySQL Mailing List" <win@lists.mysql.com> Date: Fri, Apr-22-2005 1:53 PM Subject: Re: Fulltext search with terms stored in table

That makes better sense. Thanks for the extra information. ALSO, thanks

for cluing me in that the older versions didn't support INNER JOIN... ON... I had been mentally skipping right over the part of the manual

that says that ON conditions are valid for INNER JOIN declarations only

from v3.23.17 forward.

Sorry to have misinterpreted your problem so completely (LOLl)!

Shawn Green Database Administrator Unimin Corporation - Spruce Pine

PS - Just curious: will AGAINST() take a value passed in with a variable? Can you use that for your search or will it always be a JOINed column?

"Randy Clamons" <ran@novaspace.com> wrote on 04/22/2005 03:36:11 PM:

Shawn,

Thanks for the help. You are usually right on target--but I think you missed this time.

What I actually get is "ERROR 1210: Wrong arguments to AGAINST".

A single row is selected from the 'search' table, which holds keywords for the saved search. So, when I specify search.id = 'xxx', I get only one row from the search table. I want to search the title and description columns of the items table using the keywords in the search table.

The two tables do not link together. I'm using 4.0.13-nt-log, so stored procedures are out. What I ended up doing was to retrieve the keywords into my application, then looping for each saved search, interpolating the keywords to pass to mySql as a string.

My question was--and still is--can a column from a table be used as the argument to 'AGAINST'?

BTW, this was a simplified query. The actual query joins several tables. I wrote the query with the 'comma-separated join syntax' because it will also work in earlier versions (3.xx). I have found numerous ISP's that still only provide the earlier version.

Randy Clamons Systems Programming Novaspace.com

------------Original Message------------ From: SGr@unimin.com To: ran@novaspace.com Cc: "MySQL Mailing List" <win@lists.mysql.com> Date: Fri, Apr-22-2005 6:09 AM Subject: Re: Fulltext search with terms stored in table

"Randy Clamons" <ran@novaspace.com> wrote on 04/21/2005 06:50:26

PM:

I wish to store keywords for a fulltext search in one table to search against another table. Is this possssible:

SELECT * FROM items, search WHERE search.id = 'xxx' AND MATCH (title, desctext) AGAINST (search.terms)

mySql doesn't seem to like this!

Randy Clamons Systems Programming Novaspace.com

I'll wager that the query you wrote is VERY slow, isn't it. What happened is that you forgot to somehow link the two tables which is SO easy

to

do with the comma-separated join syntax you used. If you had used an explicit INNER JOIN instead, the lack of an ON clause would have been readily apparent.

SELECT * FROM items INNER JOIN search ON search.<fieldnamehere> = items.<fieldnamehere> WHERE search.id = 'xxx' AND MATCH (title, desctext) AGAINST (search.terms)

This should be much faster as you will not need to evaluate the WHERE conditions against nearly so many record combinations.

Shawn Green Database Administrator Unimin Corporation - Spruce Pine