• 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

    • Two Xbox Ally Windows gaming handhelds announced, coming out in 2025 by Pulasthi Ariyasinghe Following years of rumors, Microsoft today announced two handhelds for the gaming crowd under the Xbox brand. Coming in as a collaboration with ASUS, the new ROG Xbox Ally and ROG Xbox Ally X are said to be aiming to "combine the power of Xbox with the freedom you expect from Windows." The new hardware is built on the AMD Ryzen Z2 A (ROG Xbox Ally) and AMD Ryzen AI Z2 Extreme (ROG Xbox Ally X) processors. Here's the complete specs sheet for both devices: Specification ROG Xbox Ally ROG Xbox Ally X Operating System Windows 11 Home Windows 11 Home Comfort & Input Contoured grips inspired by Xbox Wireless Controllers deliver all-day comfort. ABXY buttons / D-pad / L & R Hall effect analog triggers / L & R bumpers / Xbox button / View button / Menu button / Command Center button / Library button / 2x assignable back buttons / 2x full-size analog sticks / HD haptics / 6-Axis IMU Contoured grips inspired by Xbox Wireless Controllers deliver all-day comfort, complete with impulse triggers for enhanced control. ABXY buttons / D-pad / L & R impulse triggers / L & R bumpers / Xbox button / View button / Menu button / Command Center button / Library button / 2x assignable back buttons / 2x full-size analog sticks / HD haptics / 6-Axis IMU Processor AMD Ryzen™ Z1 A Processor AMD Ryzen™ AI Z1 Extreme Processor Memory 16GB LPDDR5X-6400 24GB LPDDR5X-8000 Storage 512GB M.2 2280 SSD for easier upgrade 1TB M.2 2280 SSD for easier upgrade Display 7" FHD (1080p) IPS, 500 nits, 16:9, 120Hz refresh rate, FreeSync Premium, Corning Gorilla Glass Victus + DXC Anti-Reflection 7" FHD (1080p) IPS, 500 nits, 16:9, 120Hz refresh rate, FreeSync Premium, Corning Gorilla Glass Victus + DXC Anti-Reflection I/O Ports 2x USB 3.2 Gen 2 Type-C with DisplayPort™ 2.1 / Power Delivery 3.0 1x UHS-II microSD card reader (supports SD, SDXC, SDHC) 1x 3.5mm Combo Audio Jack 1x USB4 Type-C with DisplayPort™ 2.1 / Power Delivery 3.0, Thunderbolt™ 4 compatible 1x USB 3.2 Gen 2 Type-C with DisplayPort™ 2.1 / Power Delivery 3.0 1x UHS-II microSD card reader (supports SD, SDXC, SDHC; UHS-I with DDR200 mode) 1x 3.5mm Combo Audio Jack Network and Communication Wi-Fi 6E (2 × 2) + Bluetooth 5.4 Wi-Fi 6E (2 × 2) + Bluetooth 5.4 Dimensions 290.8 × 121.5 × 50.7mm 290.8 × 121.5 × 50.7mm Weight 670g 715g Battery 60Wh 80Wh Included ROG Xbox Ally 65W charger Stand ROG Xbox Ally X 65W charger Stand According to Microsoft, the Xbox Ally handhelds will be running optimized Windows 11 versions that offer quick and easy access to tools like Xbox Game Bar and ASUS’s Armoury Crate. The company also highlighted that being Windows means that any other storefront on the platform, like Steam, GOG, and Epic Games Store, and Game Pass, will be available seamlessly. The Xbox Ally and Xbox Ally X are launching during the 2025 holiday season. Pricing details, accessories, and pre-order information will be coming later.
    • As I've said: right now Edge is better at blocking ads than Chrome, and I'll look for another browser when it changes.
    • It gave OEMs all kinds of stuff to put effort into that did nothing about bad drivers that crashed the system.
    • Yes, because Google's ad platform dominates the internet and most sites use Google's ad platform. Microsoft cares about their own ad platform. And they whitelist their ads. Edge is still on mv2 on desktop but they have officially announced they will stop supporting it. They haven't announced the date, but it is on their roadmap. Microsoft HAS the resources to keep it, but they have announced they will remove it unlike other chromium based browsers like Brave and Opera which have announced they will try to keep it. They postponing it in an attempt maybe to gain some market share from Chrome, but their end goal is the same, the deprecation of mv2. https://learn.microsoft.com/en-us/microsoft-edge/extensions-chromium/developer-guide/manifest-v3#manifest-timeline-for-microsoft-edge-and-partner-center
    • I'll say this again:  This hasn't changed since Windows 10.  This customization issue is not unique to Windows 11.   Windows 10 was released about 10 years ago.  I didn't look at changing default fonts in Windows 8 or 7.  Most (sane) people would look for supportability -- you might have the desired customization in those OSs but not able to play games, apps, that one typically gets the OS for.  No one is going to trade off getting an ancient OS just so they can have larger fonts but not be able to play games or run apps.   There are many options that are not exposed in the default UI because they have a lot more potential harm than benefit.  Doesn't mean they don't exist.  Hence, registry changes. "What's the harm by leaving it in Settings?"  Imagine if you changed the default font to something unreadable.  How would you change it back if you can't read anything?  The settings UI allows one to change size and style, but not font, so you'd still be able to read it.  Changing the font itself to Wingdings might render an OS unusable. Now YOU might be savvy enough to make that change and/or undo it, but that's why it's not exposed in simplistic UI and instead is moved to registry changes. Your 3rd party app is most likely causing conflict with the registry as it wants to make its own changes.  It's not voodoo magic here, that's typically what these apps do.  I'd bet you a beer if I spin up a new VM for Windows 11 and try my links above with no Winaero Tweaker it'd work just fine.  Introduction of 3rd party apps is always suspect -- who knows what else it's doing.  
  • Recent Achievements

    • Dedicated
      Epaminombas earned a badge
      Dedicated
    • Veteran
      Yonah went up a rank
      Veteran
    • First Post
      viraltui earned a badge
      First Post
    • Reacting Well
      viraltui earned a badge
      Reacting Well
    • Week One Done
      LunaFerret earned a badge
      Week One Done
  • Popular Contributors

    1. 1
      +primortal
      480
    2. 2
      +FloatingFatMan
      264
    3. 3
      snowy owl
      232
    4. 4
      ATLien_0
      231
    5. 5
      Edouard
      172
  • Tell a friend

    Love Neowin? Tell a friend!