[mysql] fulltext across multiple tables

[+Nik Louch 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:

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

[DaveLegg Developer]

At a guess, what you have is a fulltext index of the three columns of the product db combined, not the individual columns. Do this, and then try your search query again:

ALTER TABLE products ADD FULLTEXT(product_name), FULLTEXT(product_shortDescription), FULLTEXT(product_MRNumber);

[+Nik Louch Subscriber²]

DaveLegg: Perfect, that 100% solved the first issue. I didn't realise it made a FULLTEXT index of the combined fields - I used the ALTER command wrongly without reading up on it.

So now, the second issue...

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%')

[DaveLegg Developer]

Your syntax looks ok there, are the tables in the two queries supposed to be different? And if so, are you 100% sure the data you are searching for is in the table you're using?

[+Nik Louch Subscriber²]

My bad, yes, they are the same tables, I simply retyped them for a more logical name in this thread.

To clarify:

This returns 0 results:

SELECT * FROM productReviews WHERE MATCH(review_description) AGAINST ('deck')

This returns 7 results

SELECT * FROM productReviews WHERE review_description LIKE ('%deck%')

Weirdly:

This returns 4 results:

SELECT * FROM _tbl_inventory_product_reviews WHERE MATCH(review_description) AGAINST ('portable')

This returns 4 results:

SELECT * FROM _tbl_inventory_product_reviews WHERE review_description LIKE ('%portable%')

[+Nik Louch Subscriber²]

This may be of relevance:

  Quote

A few restrictions affect MySQL FULLTEXT indices. Some of the default behaviors of these restrictions can be changed in your my.cnf or using the SET command.

* FULLTEXT indices are NOT supported in InnoDB tables.

* MySQL requires that you have at least three rows of data in your result set before it will return any results.

* By default, if a search term appears in more than 50% of the rows then MySQL will not return any results.

* By default, your search query must be at least four characters long and may not exceed 254 characters.

* MySQL has a default stopwords file that has a list of common words (i.e., the, that, has) which are not returned in your search. In other words, searching for the will return zero rows.

* According to MySQL's manual, the argument to AGAINST() must be a constant string. In other words, you cannot search for values returned within the query.

Source

[+Nik Louch Subscriber²]

Specifically that "deck" appears in over 50% of the results...

[bolerodan]

Im just curious. With your first example searching against "deck" what happens if you try to search "in boolean mode"

SELECT * FROM productReviews WHERE MATCH(review_description) AGAINST ('deck', IN BOOLEAN MODE);

I had an issue similar to this in the past.

[+Nik Louch Subscriber²]

The issue was to do with the fact that the word DECK appeared in >50% of the total rows. As such, FULLTEXT sees it as a stopword.

[+Nik Louch Subscriber²]

Also, I am searching across those 3 tables, which use LEFT JOINs (as a review or description may not exist). I want to use FULLTEXT to achieve this, but I am getting very much varied results.