• 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

    • Hasleo Backup Suite Free 5.4.2.1 by Razvan Serea Hasleo Backup Suite Free is a free Windows backup and restore software, which embeds backup, restore and cloning features, it is designed for Windows operating system users and can be used on both Windows PCs and Servers. The backup and restore feature of Hasleo Backup Suite can help you back up and restore the Windows operating systems, disks, partitions and files (folders) to protect the security of your Windows operating system and personal data. The cloning feature of Hasleo Backup Suite can help you migrate Windows to another disk, or easily upgrade a disk to an SSD or a larger capacity disk. System Backup & Restore / Disk/Partition Backup & Restore Backup Windows operating system and boot-related partitions, including user settings, drivers and applications installed in these partitions, which ensures that you can quickly restore your Windows operating system once it crashes. Viruses, power failure, or other unknown reasons may cause data loss, so it is a good habit to regularly back up the drive that stores important files, you can at least recover lost files from the backup image files in the event of a disaster. System Clone / Disk Clone / Partition Clone Migrate the Windows operating system from one disk to another SSD or larger disk without reinstalling Windows, applications and drivers. Clone entire disk to another disk and ensure that the contents of the source disk and the destination disk are exactly the same. Clone a partition completely to the specified location on the current disk or another disk and ensure that the data will not be changed. File Backup & Restore Back up specified files(folders) instead of the entire drive to another location to protect your data, so you can quickly restore files(folders) from the backup image files when needed. Incremental/Differential/Full Backup Different backup modes are supported, you can flexibly choose data protection schemes, which can improve backup performance and save storage space while ensuring data security. Delta Restore Delta restore uses advanced delta detection technology to check the changed blocks on the destination drive and restore only the changed blocks, so it has a faster restore speed than the traditional full restore. Universal Restore This feature can help us restore the Windows operating system to computers with different hardware and ensure that Windows can work normally without any hardware compatibility issues. Hasleo Backup Suite 5.4.2.1 changelog: The program crashes when sending emails Application notifications cannot be displayed in the Windows Notification Center Updated Italian and German translations Fixed other minor bugs Download: Hasleo Backup Suite 5.4.2.1 | 33.9 MB (Freeware) Links: Hasleo Backup Suite Website | Hasleo Backup Suite Guide | Screenshot Get alerted to all of our Software updates on Twitter at @NeowinSoftware
    • 99% of Control Panel will be moved to Settings. Then by 2050, 20% of settings will have been moved to the Configuration Menu. I have no issues with Settings as it exists now in Windows 11. Bring everything over and be done with it.
    • We collect the few carcasses we get (as long as drivers report them, as they should) and while we have our fair share, we don't have nearly enough crashes with deer "to paint the roads with deer carcasses" in Finland. Also, if you can't handle the risks, DO NOT DRIVE. I really don't want everything to be so simple and stupid, that I don't have to worry about my surroundings (I also hate Apple devices because they kind of decide everything for you and offer very little customization vs. Android). Most people probably encounter much higher risks and dangers in their daily jobs than they encounter on roads. Just a few weeks ago I twisted my anckle at work while walking and I haven't had even a near miss with an animal in traffic for many years despite driving something like 27000+ km/year (there were a couple of off years in there too) and seeing many deer, a few moose and many smaller creatures on the road. I find it extremely rare to have deer stumble directly in front of me - it happens, but in my opinion not nearly enough to warrant considering it super dangerous (I actually find it exhilarating when it does happen as it changes the daily commute and it actually requires me to stay focused). It is probably more common to have some idiot with their face glued to their phone wonder in front of you or a kid on an electric scooter disregard all traffic rules. Here in the Nordics we also have plenty of snow and that kind of f's up anything that relies on lines or other clear lane indicators. The one time I have (kind of) started raging while driving was when I had a loan car from service and it had lane guidance. That freaking thing basically felt like it wanted to hit every pothole and bump it could and I really, really freaking hated it (came close to ripping the whole steering wheel of, I tell you . Didn't feel safer at all, quite the contrary, and it distracted me from the road more than anything else I've driven before, constantly fighting that f'ing thing to go where I wanted it to go (no clear lines, a crack in the pavement, etc. and it became confused as hell and required more adjusting than any traditional car). Also noteworthy that globally the amount of people with driver's licenses is pretty low (like under 20 %), many countries have great public transport systems and many walk and cycle (even during the winter).
    • The snipping tool has come a long way. Every feature they add reduces the need for third-party utilities; in the past it seemed MS shied away from adding features to some in-box tools just so there would be a more robust third-party developer base. Now that we finally have boxes, circles and arrows, can they finally add text bubbles?
  • Recent Achievements

    • Week One Done
      BlakeBringer earned a badge
      Week One Done
    • Week One Done
      Helen Shafer earned a badge
      Week One Done
    • First Post
      emptyother earned a badge
      First Post
    • Week One Done
      Crunchy6 earned a badge
      Week One Done
    • One Month Later
      KynanSEIT earned a badge
      One Month Later
  • Popular Contributors

    1. 1
      +primortal
      660
    2. 2
      ATLien_0
      266
    3. 3
      Michael Scrip
      235
    4. 4
      Steven P.
      164
    5. 5
      +FloatingFatMan
      153
  • Tell a friend

    Love Neowin? Tell a friend!