• 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

    • Hmm, I'll give it a go
    • Is Samsung teasing a Galaxy Z Fold7 Ultra? An official press release suggests so by Sagar Naresh Bhavsar We are inching closer to the launch of this year's premium foldables from Samsung: the Galaxy Z Fold7 and the Galaxy Z Flip7. Recently, a certification revealed that the affordable Galaxy Z Flip7 FE, could also debut alongside the other two foldables. The device is expected to take on the standard Moto Razr 2025. While we were getting excited about the trio, Samsung has put out a press release to create some hype around the upcoming foldables. Interestingly, the company has put great emphasis on the "Ultra" branding, which it usually uses for its Galaxy S-series phone and previously for Note-series phones. So, does this mean, we are getting a Galaxy Z Fold7 Ultra? Time will only tell. As for the press release, it is titled "Meet the Next Chapter of Ultra" and the first line highlights how Samsung has been listening to fans that have demanded "bigger screens, better cameras and new ways to connect and create." To churn up up the hype, Samsung added, "That’s why Galaxy’s next chapter is to provide an experience that seamlessly blends artistry and engineering to elevate everyday interactions." The GIF inside the press-release reveals what looks like the Galaxy Z Fold7 (or the Galaxy Z Fold7 Ultra, if that exists). Rumors have indicated that this year's Galaxy Z Fold7 is getting taller and wider than last year's model, thanks to a bigger display. The Z Fold7 could also feature a titanium backplate to not only reduce its thickness and make it stronger but to shed some weight as well. The company then directs the attention to its AI features such as voice controls for finding the perfect eatery or shopping place, using powerful AI-powered camera features, and so on. We will get to know more about the devices in the coming weeks. By that time, let us know your thoughts, on whether you would like an Ultra foldable or want Samsung to tweak the existing model and keep the space less-crowded.
    • A couple of friends of mine have been building Gunpla for years and got me interested, so I asked and they recommended this as a fairly good quality, very affordable, starting point. https://www.amazon.co.uk/dp/B0BGN9K1MV It was fun to build, didn't take too long, and helped me decide if I wanted to go further with the hobby, which I did.  Still only got this one built, but that's only due to time availability!
    • I've had an original, a Lite, and an OLED and I have just one question for you.  How big are your hands? I got rid of all three because, simply put, they're sized for kids hands more than anything, especially the Lite.  The Switch 2 is a fair bit larger but I'm not agreeing to Ninty's new EULA for the thing.
    • Where does he say that he'll give 99% to Africa? I agree that he said that he'll give 99% of his wealth, and you quote him saying that the majority will go to Africa. The majority of 99% does not mean all of the 99%. It could mean that he'll give just above half of that.
  • Recent Achievements

    • Week One Done
      Leonard grant earned a badge
      Week One Done
    • One Month Later
      portacnb1 earned a badge
      One Month Later
    • Week One Done
      portacnb1 earned a badge
      Week One Done
    • First Post
      m10d earned a badge
      First Post
    • Conversation Starter
      DarkShrunken earned a badge
      Conversation Starter
  • Popular Contributors

    1. 1
      +primortal
      260
    2. 2
      snowy owl
      158
    3. 3
      +FloatingFatMan
      145
    4. 4
      ATLien_0
      140
    5. 5
      Xenon
      131
  • Tell a friend

    Love Neowin? Tell a friend!