• 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

    • The list of Mac devices rumored to get macOS 26 by Hamid Ganji Apple will announce macOS 26 or macOS Tahoe at its Monday Worldwide Developers Conference. As has been reported multiple times over the past weeks, all Apple operating systems will receive a UI overhaul, a touch of AI, and new names at this year's WWDC. The logic behind the "26" in the new macOS version is that Apple aims to align its naming schedule with its 2025-2026 release cycle. The same version number will also appear across iOS, iPadOS, watchOS, and tvOS. While skipping multiple software versions raises many questions, it might help maintain consistency in the Apple ecosystem. With just two days away from the WWDC kick-off, many Mac owners might wonder if the upcoming macOS 26 is compatible with their devices. Here's the rumored list of compatible Mac devices with the macOS 26: MacBook Air (M1 and later) MacBook Pro (2019 and later) iMac (2020 and later) Mac Mini (M1 and later) Mac Studio (all models) Mac Pro (2019 and later) This report comes from MacRumors, which cites a private account on X as the source. However, the interesting thing about this list is that, according to the leaker, Apple might end software support for the MacBook Pro 13-inch (2020 model, two Thunderbolt 3 ports). For now, we should take this claim with a pinch of salt. The 2020 MacBook Pro 13-inch launched with both Intel and Apple Silicon M1 processors. It also comes with two port configurations. Apple is expected to end support for more Intel-based Mac devices this year, and this specific MacBook Pro variant might also be on Apple's kill list. WWDC 2025 kicks off on June 9, and Apple will unveil the latest version of its operating systems with an overhauled UI and a slew of AI-related features. So far, we know what Apple Watch models might get watchOS 26. Apple will announce the compatibility list of various devices at Monday's event.
    • It's a separate question to the thread, but I have VLC and qBitorrent working on W11 without any issue. Download->install->job done.
    • Currently I am using Display Port connection to monitor.   If I change the setting you show above it does make text larger, but ALL text larger not just icon text on desktop. Also is doesn't change the text weight at all. That setting leaves it very thing test. I want bold, or semibold.
    • Patch My PC - Home Updater 5.2.3.0 by Razvan Serea Patch My PC Free is a reliable tool which can quickly check your PC for outdated software. The supported third-party programs include a large number of widely-used applications, including Adobe Reader, Mozilla Firefox, Java, 7-Zip, BleachBit, Google Chrome and many more. Patch My PC Home updater features: Updates over 500 common apps check including portable apps Ability to cache updates for use on multiple machines No bloatware during installations Applications install/update silently by default no install wizard needed Optionally, disable silent install to perform a manual custom install Easy to use user interface Change updated and outdated apps color for color blindness Option to automatically kill programs before updating it Create a baseline of applications if installing on new PC’s Quickly uninstall multiple programs Scan time is usually less than 1 second Set updates to happen on a schedule Skip updates for any application you don’t want to update Suppresses restarts when performing application updates Patch My PC - Home Updater 5.2.3.0 changelog: Startup Manager New tab to manage which apps launch at startup. This helps speed up your boot time and gives you control over what runs in the background. Generate Diagnostic ZIP You can now create a diagnostic ZIP file from the About page. This helps if you need to send logs on our support forum for Home Updater. Remove Portable Apps Right-click any portable app in the App Catalog or Uninstaller page to remove it directly. Applications Added FFmpeg (Full Shared) – Portable Fing G-Helper – Portable IntelliJ IDEA Community Edition K-Lite Basic Codec Pack K-Lite Full Codec Pack K-Lite Standard Codec Pack KeePass Password Safe v1 LibreOffice Help Pack MemTest86 – Portable Nexus Vortex Nvidia Profile Inspector – Portable Pale Moon – Portable ViVeTool – Portable WinCDEmu Windows PC Health Check Wise Video Converter Applications Removed Driver Easy Download: Patch My PC 5.2.3.0 | 54.8 MB (Freeware) Download: Patch My PC Portable | 31.0 MB (Portable) View: Patch My PC Free Homepage | Screenshot Get alerted to all of our Software updates on Twitter at @NeowinSoftware
    • "For starters, Microsoft Edge is getting a media control center. This feature is intended to let you control multiple media sources from any website in a single place." Oh, I've got this Media Control and couldn't find how to disable it. I hate it when a button appears on a toolbar where there was none just before I press Play. I probably would find it at least somewhat useful if I could start playing media from any opened tab, but now it only shows controls for media I've already started playing. If anyone knows how to disable it - I'd appreciate a hint.
  • Recent Achievements

    • Week One Done
      abortretryfail earned a badge
      Week One Done
    • First Post
      Mr bot earned a badge
      First Post
    • First Post
      Bkl211 earned a badge
      First Post
    • One Year In
      Mido gaber earned a badge
      One Year In
    • One Year In
      Vladimir Migunov earned a badge
      One Year In
  • Popular Contributors

    1. 1
      +primortal
      490
    2. 2
      +FloatingFatMan
      257
    3. 3
      snowy owl
      248
    4. 4
      ATLien_0
      224
    5. 5
      +Edouard
      189
  • Tell a friend

    Love Neowin? Tell a friend!