Fulltext search with terms stored in table

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

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

mySql doesn't seem to like this!

Randy Clamons
Systems Programming
Novaspace.com


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org

Re: Fulltext search with terms stored in table

--=_alternative 004800AA85256FEB_=
Content-Type: text/plain; charset="US-ASCII"

"Randy Clamons" 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. = items.
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

--=_alternative 004800AA85256FEB_=--

Re: Fulltext search with terms stored in table

Shawn,

Thanks for the help. You are usually right on target--but I think you misse=
d 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 =3D 'xxx', I get only one ro=
w 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 pro=
cedures are out. What I ended up doing was to retrieve the keywords into my=
application, then looping for each saved search, interpolating the keyword=
s to pass to mySql as a string.

My question was--and still is--can a column from a table be used as the arg=
ument 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 on=
ly provide the earlier version.

Randy Clamons
Systems Programming
Novaspace.com

> ------------Original Message------------
> From: SGreen@unimin.com
> To: randy@novaspace.com
> Cc: "MySQL Mailing List"
> Date: Fri, Apr-22-2005 6:09 AM
> Subject: Re: Fulltext search with terms stored in table
>
> "Randy Clamons" 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 =3D '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. =3D items.
> WHERE search.id =3D '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
> =



--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org

Re: Fulltext search with terms stored in table

--=_alternative 007280C685256FEB_=
Content-Type: text/plain; charset="US-ASCII"

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" 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: SGreen@unimin.com
> > To: randy@novaspace.com
> > Cc: "MySQL Mailing List"
> > Date: Fri, Apr-22-2005 6:09 AM
> > Subject: Re: Fulltext search with terms stored in table
> >
> > "Randy Clamons" 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. = items.
> > 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
> >
>

--=_alternative 007280C685256FEB_=--

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 argumen=
t to AGAINST() must be a constant string. (manual.html#Fulltext_Restriction=
s).

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

Randy Clamons
Systems Programming
Novaspace.com


> ------------Original Message------------
> From: SGreen@unimin.com
> To: randy@novaspace.com
> Cc: "MySQL Mailing List"
> 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" 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 =3D '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: SGreen@unimin.com
> > > To: randy@novaspace.com
> > > Cc: "MySQL Mailing List"
> > > Date: Fri, Apr-22-2005 6:09 AM
> > > Subject: Re: Fulltext search with terms stored in table
> > >
> > > "Randy Clamons" 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 =3D '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. =3D items.
> > > WHERE search.id =3D '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
> > > =

> > =

> =



--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org

RE: Fulltext search with terms stored in table

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:randy@novaspace.com]=20
Sent: Saturday, 23 April 2005 7:13 AM
To: SGreen@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: SGreen@unimin.com
> To: randy@novaspace.com
> Cc: "MySQL Mailing List"
> 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=20
>=20
> for cluing me in that the older versions didn't support INNER JOIN...=20
> 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=20
>=20
> from v3.23.17 forward.
>=20
> Sorry to have misinterpreted your problem so completely (LOLl)!
>=20
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>=20
> PS - Just curious: will AGAINST() take a value passed in with a=20
> variable?=20
> Can you use that for your search or will it always be a JOINed column?
>=20
>=20
>=20
> "Randy Clamons" wrote on 04/22/2005 03:36:11 PM:
>=20
> >=20
> > Shawn,
> >=20
> > Thanks for the help. You are usually right on target--but I think=20
> > you missed this time.
> >=20
> > What I actually get is "ERROR 1210: Wrong arguments to AGAINST".
> >=20
> > A single row is selected from the 'search' table, which holds=20
> > keywords for the saved search. So, when I specify search.id =3D =
'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.
> >=20
> > The two tables do not link together. I'm using 4.0.13-nt-log, so=20
> > stored procedures are out. What I ended up doing was to retrieve the
> > keywords into my application, then looping for each saved search,=20
> > interpolating the keywords to pass to mySql as a string.
> >=20
> > My question was--and still is--can a column from a table be used as=20
> > the argument to 'AGAINST'?
> >=20
> > BTW, this was a simplified query. The actual query joins several=20
> > tables. I wrote the query with the 'comma-separated join syntax'=20
> > because it will also work in earlier versions (3.xx). I have found=20
> > numerous ISP's that still only provide the earlier version.
> >=20
> > Randy Clamons
> > Systems Programming
> > Novaspace.com
> >=20
> > > ------------Original Message------------
> > > From: SGreen@unimin.com
> > > To: randy@novaspace.com
> > > Cc: "MySQL Mailing List"
> > > Date: Fri, Apr-22-2005 6:09 AM
> > > Subject: Re: Fulltext search with terms stored in table
> > >
> > > "Randy Clamons" wrote on 04/21/2005 06:50:26

> PM:
> > >=20
> > > > I wish to store keywords for a fulltext search in one table to=20
> > > > search against another table. Is this possssible:
> > > >=20
> > > > SELECT *
> > > > FROM items, search
> > > > WHERE search.id =3D 'xxx'
> > > > AND MATCH (title, desctext) AGAINST (search.terms)
> > > >=20
> > > > mySql doesn't seem to like this!
> > > >=20
> > > > Randy Clamons
> > > > Systems Programming
> > > > Novaspace.com
> > > >=20
> > > >=20
> > > I'll wager that the query you wrote is VERY slow, isn't it. What=20
> > > happened=20
> > > is that you forgot to somehow link the two tables which is SO easy

> to=20
> > > do=20
> > > with the comma-separated join syntax you used. If you had used an=20
> > > explicit=20
> > > INNER JOIN instead, the lack of an ON clause would have been=20
> readily=20
> > > apparent.
> > >=20
> > > SELECT *
> > > FROM items
> > > INNER JOIN search
> > > ON search. =3D items.
> > > WHERE search.id =3D 'xxx'
> > > AND MATCH (title, desctext) AGAINST (search.terms)
> > >=20
> > > This should be much faster as you will not need to evaluate the=20
> WHERE=20
> > > conditions against nearly so many record combinations.
> > >=20
> > > Shawn Green
> > > Database Administrator
> > > Unimin Corporation - Spruce Pine
> > >=20
> >=20
>=20


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org