• 0

[mysql] fulltext across multiple tables


Question

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

9 answers to this question

Recommended Posts

  • 0

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);

  • 0

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

  • 0

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

  • 0

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

  • 0

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.

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Posts

    • They always have. This just updates what's included out of the box during the initial install.
    • So basically their prebuilding it into the ISOs?
    • Microsoft shares Defender anti-virus update for new Windows 11/10 ISOs by Sayan Sen Following the one for Lumma last month, Microsoft has also published a new Defender update for Windows 11/10/Server installation images this month. This update package is necessary as a Windows installation image may contain old, outdated anti-malware definitions and software binaries. Aside from better security, these updates can also provide improved performance benefits in some cases. When a new Windows installation is set up, there may be a temporary security risk due to outdated Microsoft Defender protection in the OS installation images. This happens because the antimalware software included in these images might not be up to date. Thus Microsoft says that these updated definitions essentially help close this protection gap. Microsoft delivered the latest security definitions for Windows images via security intelligence update version 1.431.54.0. The Defender package version is also the same. It applies to Windows 11, Windows 10 (Enterprise, Pro, and Home editions), Windows Server 2022, Windows Server 2019, and Windows Server 2016. Microsoft writes: From Microsoft's security bulletin, we learn that the security intelligence update version 1.431.54.0 was released this past week adds threat detections for various backdoor exploits, trojans, among others. For those wondering, the latest intelligence update is version 1.431.155.0 at the time of writing.
    • “In other news about OneDrive frustration, Microsoft recently locked out a user who was attempting to move a bunch of important data from old hard drives to OneDrive.” - It was probably CSMA..
    • Just what I needed! An extremely expensive pair of sunglasses that I will lose and cannot use indoors. My life will be complete. I trust Meta AI watching my entire life happen. It's going to be the best ever!
  • Recent Achievements

    • One Month Later
      SekTheFirst earned a badge
      One Month Later
    • First Post
      zayanhani earned a badge
      First Post
    • First Post
      HarryTaylor earned a badge
      First Post
    • One Year In
      Eternal Tech earned a badge
      One Year In
    • One Month Later
      Eternal Tech earned a badge
      One Month Later
  • Popular Contributors

    1. 1
      +primortal
      651
    2. 2
      ATLien_0
      232
    3. 3
      Michael Scrip
      224
    4. 4
      Steven P.
      140
    5. 5
      +FloatingFatMan
      138
  • Tell a friend

    Love Neowin? Tell a friend!