Re: Fulltext search with terms stored in table - Randy Clamons - com.mysql.lists.win32

6 messages in com.mysql.lists.win32Re: Fulltext search with terms stored...

From	Sent On	Attachments
Randy Clamons	21 Apr 2005 15:50
SGr...@unimin.com	22 Apr 2005 06:02
Randy Clamons	22 Apr 2005 12:35
SGr...@unimin.com	22 Apr 2005 13:47
Randy Clamons	22 Apr 2005 14:42
jbon...@sola.com.au	25 Apr 2005 15:53

Subject:	Re: Fulltext search with terms stored in table
From:	Randy Clamons (ran...@novaspace.com)
Date:	04/22/2005 12:35:49 PM
List:	com.mysql.lists.win32

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.

------------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!

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.