• 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

    • 1. DRM is the worst plague, I like my games clean. 2. Third party launchers are an overhead and annoyance.
    • Amazon lays off more staff across Goodreads and Kindle divisions by Hamid Ganji Dozens of Amazon employees working on the retailer's book divisions have been laid off. As reported by Reuters, Amazon confirmed that it's cutting jobs across the Goodreads review site and Kindle units, which impacts fewer than 100 workers. Amazon says the recent layoffs across Goodreads and Kindle divisions are meant to improve efficiency and streamline operations. The giant retailer has constantly reduced staff across various divisions over the past few years. According to CEO Andy Jassy, reducing headcounts helps the company to eliminate bureaucracy. "As part of our ongoing work to make our teams and programs operate more efficiently and to better align with our business roadmap, we've made the difficult decision to eliminate a small number of roles within the Books organization," an Amazon spokesperson said. Layoffs recently impacted employees in Amazon's Wondery podcast division, devices and services units, communications, and in-store staff. However, Amazon's Q1 results show the retailer has added about 4,000 jobs compared to Q4 2024. After the Covid pandemic settled down, many companies began laying off thousands of staff they hired during the pandemic to respond to growing demands. The layoff trend among tech firms still exists today, and AI has amplified it. The latest data shows that in 2025, about 62,832 tech employees were laid off across 141 tech companies. Also, 152,922 tech employees across 551 companies were laid off in 2024. More layoffs are expected to occur due to declining economic growth, tariffs, and the expansion of AI across companies. Amazon is also gearing up to double down in AI investments and robotics. The company has recently announced the forming of a new agentic AI team to develop an agentic AI framework for use in robotics. Also, a new report by The Information indicates that Amazon has begun testing humanoid robots for package delivery.
    • Major Privacy 0.98.1.1 Beta by Razvan Serea MajorPrivacy is a cutting-edge privacy and security tool for Windows, offering unparalleled control over process behavior, file access, and network communication. It is a continuation of the PrivateWin10 project. By leveraging advanced kernel-level protections, MajorPrivacy creates a secure environment where user data and system integrity are fully safeguarded. Unlike traditional tools, MajorPrivacy introduces innovative protection methods that ensure mounted encrypted volumes are only accessible by authorized applications, making it the first and only encryption solution of its kind. MajorPrivacy – Ultimate Privacy & Security for Windows key features Process Protection – Isolate processes to block interference from unauthorized apps, even with admin privileges. Software Restriction – Block unwanted apps and DLLs to ensure only trusted software runs. Revolutionary Encrypted Volumes Secure Storage – Create encrypted disk images for sensitive data. Exclusive Access – Unlike traditional tools, only authorized apps can access mounted volumes—blocking all unauthorized processes. File & Folder Protection – Lock down sensitive files and prevent unauthorized access or modifications. Advanced Network Firewall – Control which apps can send or receive data online. DNS Monitoring & Filtering – Track domain access and block unwanted sites (Pi-hole compatible filtering coming soon). Tweak Engine – Disable telemetry, cloud integration, and invasive Windows features for better privacy. Why MajorPrivacy? Kernel-Level Security – Protects at the deepest system level. Unmatched Encryption Protection – Keeps mounted volumes safe from all unauthorized access. Full System Control – Block, isolate, or restrict processes as needed. Enhanced Privacy – Stops Windows & apps from collecting unnecessary data. Perfect for privacy-conscious users, IT pros, and anyone who wants total system control. Major Privacy 0.98.1.1 Beta changelog: The 0.98.1 release of MajorPrivacy introduces significant enhancements and a number of critical fixes aimed at improving usability, localization, and system integration. A major new feature is the introduction of full translation support, allowing the application interface and tweaks to be localized into multiple languages. Initial translations include AI-assisted German and Polish versions, a community-contributed Turkish translation, and Simplified Chinese. Users interested in contributing translations or adding new languages are encouraged to participate via the forum. This version also improves compatibility and deployment by bundling the Microsoft Visual C++ Redistributable with the installer, which is required for the ImDisk user interface. Several important bugs have been resolved. The installer now correctly removes the driver during uninstallation. Tweak definitions have been cleaned up for better consistency. A number of networking issues were addressed, including failures related to network shares and incorrect handling of mapped drive letters. It is now required to use full UNC paths for defining rules involving shared resources. Additionally, configuration persistence issues on system shutdown have been fixed, as well as problems affecting protected folder visibility and rule precedence involving enclave conditions. Finally, the underlying driver code has been refactored, laying the groundwork for better maintainability and future enhancements. MajorPrivacy-v0.98.1.1.exe (0.98.1a) hotfix for #71 Download: Major Privacy 0.98.1.1 Beta | 47.4 MB (Open Source) View: MajorPrivacy Home Page | Github Project page | Screenshot Get alerted to all of our Software updates on Twitter at @NeowinSoftware
  • Recent Achievements

    • First Post
      Uranus_enjoyer earned a badge
      First Post
    • Week One Done
      Uranus_enjoyer earned a badge
      Week One Done
    • Week One Done
      jfam earned a badge
      Week One Done
    • First Post
      survivor303 earned a badge
      First Post
    • Week One Done
      CHUNWEI earned a badge
      Week One Done
  • Popular Contributors

    1. 1
      +primortal
      430
    2. 2
      +FloatingFatMan
      239
    3. 3
      snowy owl
      212
    4. 4
      ATLien_0
      211
    5. 5
      Xenon
      157
  • Tell a friend

    Love Neowin? Tell a friend!