SitePoint Sponsor |
|
User Tag List
Results 1 to 13 of 13
Thread: extending FULLTEXT searches
-
Mar 15, 2002, 00:22 #1
- Join Date
- Dec 2001
- Location
- Kuala Belait, Brunei
- Posts
- 367
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
extending FULLTEXT searches
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.
pPhilip Toews Professional esl Educator and ASP.NET wannabe
http://www.philiptoews.com
philip@philiptoews.com
-
Mar 15, 2002, 01:16 #2
- Join Date
- Apr 2000
- Location
- Melbourne, Australia
- Posts
- 2,571
- Mentioned
- 2 Post(s)
- Tagged
- 0 Thread(s)
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.Kevin Yank
CTO, sitepoint.com
I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
Baby’s got back—a hard back, that is: The Ultimate CSS Reference
-
Mar 15, 2002, 03:45 #3
- Join Date
- Dec 2001
- Location
- Kuala Belait, Brunei
- Posts
- 367
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
OK..just two more questions then
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.
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.Philip Toews Professional esl Educator and ASP.NET wannabe
http://www.philiptoews.com
philip@philiptoews.com
-
Mar 15, 2002, 09:27 #4
- Join Date
- Apr 2000
- Location
- Melbourne, Australia
- Posts
- 2,571
- Mentioned
- 2 Post(s)
- Tagged
- 0 Thread(s)
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.Kevin Yank
CTO, sitepoint.com
I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
Baby’s got back—a hard back, that is: The Ultimate CSS Reference
-
Mar 16, 2002, 04:24 #5
- Join Date
- Dec 2001
- Location
- Kuala Belait, Brunei
- Posts
- 367
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
So how does one merge query results?
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?
pPhilip Toews Professional esl Educator and ASP.NET wannabe
http://www.philiptoews.com
philip@philiptoews.com
-
Mar 16, 2002, 12:53 #6
- Join Date
- Apr 2000
- Location
- Melbourne, Australia
- Posts
- 2,571
- Mentioned
- 2 Post(s)
- Tagged
- 0 Thread(s)
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 DESCKevin Yank
CTO, sitepoint.com
I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
Baby’s got back—a hard back, that is: The Ultimate CSS Reference
-
Mar 18, 2002, 05:36 #7
- Join Date
- Dec 2001
- Location
- Kuala Belait, Brunei
- Posts
- 367
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
thanks Kevin
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
pPhilip Toews Professional esl Educator and ASP.NET wannabe
http://www.philiptoews.com
philip@philiptoews.com
-
Mar 28, 2002, 11:33 #8
- Join Date
- Dec 2001
- Location
- Kuala Belait, Brunei
- Posts
- 367
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
trouble joining tables in FULLTEXT search
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;
Unknown table 'grammar_sections' in field list
pPhilip Toews Professional esl Educator and ASP.NET wannabe
http://www.philiptoews.com
philip@philiptoews.com
-
Mar 28, 2002, 18:20 #9
- Join Date
- Jul 2001
- Location
- Missouri
- Posts
- 3,428
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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;
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;- Matt** Ignore old signature for now... **
Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
"Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR
-
Mar 29, 2002, 00:58 #10
- Join Date
- Dec 2001
- Location
- Kuala Belait, Brunei
- Posts
- 367
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Almost there....
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
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
pPhilip Toews Professional esl Educator and ASP.NET wannabe
http://www.philiptoews.com
philip@philiptoews.com
-
Mar 29, 2002, 03:10 #11
- Join Date
- Jul 2001
- Location
- Missouri
- Posts
- 3,428
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
May 10, 2003, 06:03 #12
- Join Date
- May 2003
- Location
- finland
- Posts
- 2
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
-
May 11, 2003, 04:03 #13
- Join Date
- May 2003
- Location
- finland
- Posts
- 2
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
Bookmarks