I am writing a search tool to search our product inventory. The relevant parts of the database structure are:
TABLE products
--product_ID(integer)
--product_name(varchar50)
--product_shortDescription(varchar255)
--product_MRNumber(varchar50)
TABLE productDescriptions
--description_ID(integer)
--product_ID(integer)
--description_text(text)
TABLE productReviews
--review_ID(integer)
--product_ID(integer)
--review_text(text)
The JOINs are kinda obvious, but the products table JOINS against both productDescriptions and productReviews on the product_ID field. Currently this is a one-to-one relationship (reviews will become one to many later, will add a user_ID field and have many reviews by many users against a single product - but that is not for now).
The fields I want to search on are:
products.product_name
products.product_shortDescription
products.product_MRNumber
productDescriptionsdescription_text
productReviewsreview_text
I have performed the following to create the FULLTEXT indices, all of which returned no errors:
ALTER TABLE products ADD FULLTEXT(product_name, product_shortDescription, product_MRNumber);
ALTER TABLE productDescriptions ADD FULLTEXT(description_text);
ALTER TABLE productReviews ADD FULLTEXT(review_text);
However, things are NOT going well...
Firstly, if I perform the following, I get an error "Can't find FULLTEXT index matching the column list":
SELECT
*
FROM
_products
WHERE
MATCH(
product_shortDescription
) AGAINST ('watt')
My GUESS is that this is down to the product_shortDescription field not being TEXT but rather VARCHAR - so no FULLTEXT can actually be applied? If so, suggestions appreciated. This shortDescription is just a very short one line description used more as a quick identifier than a full-on description.
Secondly, I am finding that certain searches are producing no results when they should be returning at least one item. For example, the following returns an empty set:
SELECT
*
FROM
productReviews
WHERE
MATCH(
review_description
) AGAINST ('deck')
whereas this returns 7 rows:
SELECT
*
FROM
_tbl_inventory_product_reviews
WHERE
review_description LIKE ('%deck%')
What am I doing wrong? I would appreciate any help - sorry if I come across "noob" - I have used FULLTEXT before in MS SQL, but not within MySql...
Question
+Dick Montage Subscriber²
I am writing a search tool to search our product inventory. The relevant parts of the database structure are:
TABLE products
--product_ID (integer)
--product_name (varchar50)
--product_shortDescription (varchar255)
--product_MRNumber (varchar50)
TABLE productDescriptions
--description_ID (integer)
--product_ID (integer)
--description_text (text)
TABLE productReviews
--review_ID (integer)
--product_ID (integer)
--review_text (text)
The JOINs are kinda obvious, but the products table JOINS against both productDescriptions and productReviews on the product_ID field. Currently this is a one-to-one relationship (reviews will become one to many later, will add a user_ID field and have many reviews by many users against a single product - but that is not for now).
The fields I want to search on are:
products.product_name
products.product_shortDescription
products.product_MRNumber
productDescriptionsdescription_text
productReviewsreview_text
I have performed the following to create the FULLTEXT indices, all of which returned no errors:
However, things are NOT going well...
Firstly, if I perform the following, I get an error "Can't find FULLTEXT index matching the column list":
SELECT * FROM _products WHERE MATCH( product_shortDescription ) AGAINST ('watt')My GUESS is that this is down to the product_shortDescription field not being TEXT but rather VARCHAR - so no FULLTEXT can actually be applied? If so, suggestions appreciated. This shortDescription is just a very short one line description used more as a quick identifier than a full-on description.
Secondly, I am finding that certain searches are producing no results when they should be returning at least one item. For example, the following returns an empty set:
SELECT * FROM productReviews WHERE MATCH( review_description ) AGAINST ('deck')whereas this returns 7 rows:
SELECT * FROM _tbl_inventory_product_reviews WHERE review_description LIKE ('%deck%')What am I doing wrong? I would appreciate any help - sorry if I come across "noob" - I have used FULLTEXT before in MS SQL, but not within MySql...
Link to comment
Share on other sites
9 answers to this question
Recommended Posts