extending FULLTEXT searches

[Philip Toews]

A FULLTEXT search is limited to one table.

All parameters to the MATCH function must be columns from the same table that is part of the same fulltext index, unless this MATCH is IN BOOLEAN MODE.

What does that mean? Sorry for the newbie question.

p

[Kevin Yank]

Consider this example: Your site has two tables: authors and articles. Each author has a description, and each article has a body text. Since you want both of these fields to be keyword-searchable, you might decide to create FULLTEXT indices for each of them.

The problem now is that a single MATCH function in a SELECT query can't search both indices at once, as they are in different tables. Instead, you need to search each table separately and combine the results in some intelligent fashion yourself.

So basically, a single FULLTEXT search can't search fields in multiple tables at once.

[Philip Toews]

What does the "BOOLEAN" part of the restriction mean? (that's a newbie question...I know...sorry).

More importantly. How do I do what you suggested?:

Instead, you need to search each table separately and combine the results in some intelligent fashion yourself.

In fact, what I want to do is pass the accumulated results to the paged results class that you describe in your tutorial on that subject. I have modifed the CLASS to accept results instead of queries, but I don't know how to merge the results of two separate queries into one result.

For that matter, what I am wondering here is how to do table joins for FULLTEXT queries. Using your tutorial example, how would I force MySQL to only SELECT the records in a given category of jokes? The way the system works (as produced by the tutorial in your book) is that categories are determined by queries to a lookup table. This neccesitates a table join in the query, n'est-ce pas? So here is the crux of my confusion. How can I set up a query using a FULLTEXT search that is restricted to only certain categories as determined by a query to a lookup table?

The FULLTEXT search capability in MySQL is very cool, but the restrictions on it make it a pain to work with. A solution to my categories inclusion question above would make it possible to extend the CMS in your book in an extremely useful way.

Any responses will be GREATLY appreciated.

[Kevin Yank]

For the BOOLEAN part of the restriction, I'd suggest reading the MySQL reference manual's section on the subject. If that doesn't explain it for you, let me know and I'll take a closer look.

As for using MATCH in SELECTs that involve table joins, there's nothing to stop you from doing this. The only restriction is that a single MATCH expression cannot span multiple tables.

[Philip Toews]

Thanks for the clarification. The ability to use table joins will be helpful, but I am still wondering how to merge results together. How do I do separate queries and then join the results together in order to pass them to things like the paged results class?

p

[Kevin Yank]

Since each MATCH expression produces a numerical value that represents the relevance of each row to the search keywords, you can take the sum of two MATCH expressions as the total relevance based on two MATCH searches.

SELECT *, MATCH (table1.col1) AGAINST ('keyword') + MATCH (table2.col2) AGAINST ('keyword') AS rel WHERE rel > 1 ORDER BY rel DESC

[Philip Toews]

I appreciate the detailed answer. It opens up a whole spate of possibilities for FULLTEXT searches.

Seems the MySQL 4.0 holds great improvements for FULLTEXT searching capabilities. Faster, more stable...maybe even a few configuarable parameters.

Does anyone have an informed notion of when that will be released? I'm waiting to buy a MySQL reference book until then....

Thanks again Kevin

p

[Philip Toews]

I have tried to follow Kevin's advice by using the following query. Both tables exist and both have FULLTEXT indexes.

Here's my query:

SELECT * , MATCH (grand_summary.long_text) AGAINST ('simple') + MATCH (grammar_sections.long_text) AGAINST ('simple') FROM grand_summary, grammar_sections AS rel WHERE rel > 1 ORDER BY rel DESC;

Here's the error message:

Unknown table 'grammar_sections' in field list

Can anyone please tell me how to adjust this query so that I can search two tables as a table join doing a FULLTEXT search?

p

[DR_LaRRY_PEpPeR]

you have your "AS rel" in the wrong place. you were aliasing grammar_sections as rel, so then it should be referred to as rel in your SELECT (e.g. rel.long_text), NOT grammar_sections. and should that not be rel > 0? here's how it should be:

SELECT *, MATCH (grand_summary.long_text) AGAINST ('simple') + MATCH (grammar_sections.long_text) AGAINST ('simple') AS rel FROM grand_summary, grammar_sections WHERE rel > 0 ORDER BY rel DESC;

however, i still don't think that will work. you'll probably get something like "Unknown column rel..." because rel is not a column. i believe you should do it like this:

SELECT *, MATCH (grand_summary.long_text) AGAINST ('simple') + MATCH (grammar_sections.long_text) AGAINST ('simple') AS rel FROM grand_summary, grammar_sections WHERE (MATCH (grand_summary.long_text) AGAINST ('simple') AND MATCH (grammar_sections.long_text) AGAINST ('simple')) ORDER BY rel DESC;

if you want to allow a match in either table, rather than both, use OR in the WHERE instead of AND.

[Philip Toews]

Thanks Dr. Pepper,

I have tried the code and I get the following FULLTEXT error:

MySQL said: Can't find FULLTEXT index matching the column list

Now I am sure that both grand_summary and grammar_sections each have a FULLTEXT index. I don't know if it is relevant, but the two tables I am working with here have a FULLTEXT index on three columns each. That is to say, they are each indexed on three columns. In both cases, the columns have the same names...NAME, SHORT_TEXT, LONG_TEXT.

What exactly is this error message telling me? That I need to list all three columns, or that it can't reconcile a FULLTEXT search on two tables?

Just a little more help <beg>please....</beg>


TIA

p

[DR_LaRRY_PEpPeR]

i don't have any experience with FULLTEXT indexes, so i didn't know if i'd be able to help with this error. however, it looks like you may need to list all columns that have the FULLTEXT index. i found this in the manual, under Full-text Restrictions:

The MATCH() column list must exactly match the column list in some FULLTEXT index definition for the table, unless this MATCH() is IN BOOLEAN MODE.

isn't that what it sounds like? hope that helps.

[faissaad1]

hi all,

I also have the same problem. I tried to use this statement

SELECT *, MATCH (products.product_name) AGAINST ('simple') + MATCH (services.service_name) AGAINST ('simple') AS rel FROM products, services WHERE (MATCH (products.product_name) AGAINST ('simple') OR MATCH (services.service_name) AGAINST ('simple')) ORDER BY rel DESC;
And I'm getting this error

Can't find FULLTEXT index matching the column list

I also have in products table a product_description and in services a service_description wich also are fulltext fields and I also wanna search them in the same select statement. How can I do that?

Thanks

[faissaad1]

Hi all,
To tell you what I'm trying to do is to make a search engine for my web. and I have these tables in my database

CREATE TABLE tuotteet (
id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
nimi VARCHAR(200) NOT NULL,
ryhma VARCHAR(200) NOT NULL,
kuvaus TEXT NOT NULL,
kuva TEXT NOT NULL,
esite TEXT,
FULLTEXT (kuvaus)
);

CREATE TABLE palvelut (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
nimi VARCHAR(200) NOT NULL,
kuvaus TEXT NOT NULL,
kuva TEXT NOT NULL,
FULLTEXT (kuvaus)
);

I'm triying to fulltext search the kuvaus fields and this is the sql

SELECT *, MATCH (tuotteet.kuvaus ) AGAINST ('korjaus') + MATCH (palvelut.kuvaus) AGAINST ('korjaus') AS rel FROM tuotteet, palvelut WHERE (MATCH (tuotteet.kuvaus) AGAINST ('korjaus') OR MATCH (palvelut.kuvaus) AGAINST ('korjaus')) ORDER BY rel DESC;

but this is returning both fields in one result.what i want is if the word korjaus found in table tuotteet field kuvaus, then the search returns that fields id name wich is link
and if it also found in the other tables kuvaus it also return in that fields id and name but in a new line.
did you understand what i mean?
if you can give me some example will be great




thanks again