How to optimise fulltext search on different table with different field in mysql?

Question

I have these 4 different tables as below.

First is the companyCategory. This is table keep all the different categories of companies

CREATE TABLE `companyCategory` (
  `companyCategoryID` int NOT NULL,
  `companyCategory` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT;



ALTER TABLE `companyCategory`
  ADD PRIMARY KEY (`companyCategoryID`);
ALTER TABLE `companyCategory` ADD FULLTEXT KEY `companyCategory` (`companyCategory`);

Second table is the company.

   CREATE TABLE `company` (
      `companyID` smallint NOT NULL,
      `segmentID` tinyint NOT NULL DEFAULT '1',
      `companyCategoryID` tinyint NOT NULL,
      `companyName` varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
      `companyNumber` varchar(15) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
      `contactPerson` varchar(20) NOT NULL,
      `companyAddress` varchar(70) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
`companyLatitude` float(10,8) NOT NULL,
`companyLongitude` float(11,8) NOT NULL,
      `companyContactNumber` varchar(15) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
      `companyEmail` varchar(35) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
      `companyDescription` varchar(50) DEFAULT NULL,
      `companyTimeZoneID` tinyint UNSIGNED NOT NULL,  
      `companyInsertUserID` mediumint DEFAULT NULL,
      `companyStatus` enum('a','d') CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT;



    ALTER TABLE `company`
      ADD PRIMARY KEY (`companyID`),
      ADD UNIQUE KEY `companyNameIndex` (`companyName`);
    ALTER TABLE `company` ADD FULLTEXT KEY `companyName` (`companyName`);

Third table is itemCategory.

CREATE TABLE `itemCategory` (
  `itemCategoryID` smallint UNSIGNED NOT NULL,
  `companyID` smallint UNSIGNED NOT NULL,
  `itemCategory` varchar(35) NOT NULL,
  `itemCategoryStatus` enum('a','d') NOT NULL,
  `userID` mediumint NOT NULL,
  `dateTimeInsert` timestamp NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;



ALTER TABLE `itemCategory`
  ADD PRIMARY KEY (`itemCategoryID`),
  ADD UNIQUE KEY `companyID` (`companyID`,`itemCategory`);
ALTER TABLE `itemCategory` ADD FULLTEXT KEY `itemCategory` (`itemCategory`);

Fourth table is

CREATE TABLE `item` (
  `itemID` smallint NOT NULL,
  `itemCategoryID` smallint NOT NULL,
  `companyID` smallint NOT NULL,
  `itemName` varchar(35) NOT NULL,
  `itemDescription` varchar(50) DEFAULT NULL,
  `itemPrice` decimal(5,2) NOT NULL,
  `itemStatus` enum('a','d') NOT NULL,
  `userID` smallint NOT NULL,
  `dateTimeInsert` timestamp NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;



ALTER TABLE `item`
  ADD PRIMARY KEY (`itemID`),
  ADD UNIQUE KEY `companyID` (`companyID`,`itemName`);
ALTER TABLE `item` ADD FULLTEXT KEY `itemName` (`itemName`);

Now my challenge is that I have enable Fulltext on each of these table. The first link is between company and companyCategory based on companyCategoryID.

Next the link between itemCategory and item based on the itemCategoryID. Both these table also have companyID as it links to the company table. Now my challenge is say user put a key word.

The keyword can be search across all these table. Below is the query which I have tested. When I run explain it does not show the fullindex is being used. At minimal it should the company name if it match the company name or companyCategory. Next level if it matches the item category or item only if there is item is matching

select company.companyID,item.itemName

FROM company

JOIN companyCategory ON
company.companyCategoryID=companyCategory.companyCategoryID
JOIN itemCategory ON
company.companyID = itemCategory.companyID
JOIN item ON
company.companyID = item.companyID
WHERE

MATCH(company.companyName) AGAINST('*foo*' IN BOOLEAN MODE)
OR MATCH(companyCategory.companyCategory) AGAINST('*foo*' IN BOOLEAN MODE)
OR MATCH(itemCategory.itemCategory) AGAINST('*foo*' IN BOOLEAN MODE)
OR MATCH(item.itemName) AGAINST('*foo*' IN BOOLEAN MODE)

Here is my new version of the query with union new solution.

SELECT  allValue.cm AS cName,allValue.companyID AS cID,allValue.companyCategory As cCategory,(allValue.distance/1000) as distance
                        FROM  ( 
                                (select item.itemName as itemName,null,company.companyName as cm,company.companyID as companyID,companyCategory.companyCategory,ST_Distance_Sphere( ST_GeomFromText(:userPoint1a), ST_GeomFromText(CONCAT('POINT(', company.companyLongitude, ' ', company.companyLatitude, ')'))) as distance
                                From item
                                JOIN company ON
                                item.companyID=company.companyID
                                JOIN companyCategory ON 
                                companyCategory.companyCategoryID = company.companyCategoryID 
                                WHERE                         
                                MATCH(item.itemName) AGAINST(:searchKey1 IN BOOLEAN MODE) AND ST_Distance_Sphere( ST_GeomFromText(:userPoint1b), ST_GeomFromText(CONCAT('POINT(', company.companyLongitude, ' ', company.companyLatitude, ')'))) < 200000
                                )

                                UNION DISTINCT

                                (
                                select null,null,company.companyName as cm,company.companyID as companyID,companyCategory.companyCategory,ST_Distance_Sphere( ST_GeomFromText(:userPoint2a), ST_GeomFromText(CONCAT('POINT(', company.companyLongitude, ' ', company.companyLatitude, ')'))) as distance
                                From company 
                                JOIN companyCategory ON
                                companyCategory.companyCategoryID=company.companyCategoryID          
                                WHERE                         
                                MATCH(company.companyName) AGAINST(:searchKey2 IN BOOLEAN MODE) AND ST_Distance_Sphere( ST_GeomFromText(:userPoint2b), ST_GeomFromText(CONCAT('POINT(', company.companyLongitude, ' ', company.companyLatitude, ')'))) < 200000
                                )

                                UNION DISTINCT

                                (
                                select null,null,company.companyName as cm,company.companyID as companyID,companyCategory.companyCategory,ST_Distance_Sphere( ST_GeomFromText(:userPoint3a), ST_GeomFromText(CONCAT('POINT(', company.companyLongitude, ' ', company.companyLatitude, ')'))) as distance
                                From itemCategory
                                JOIN company ON
                                itemCategory.companyID=company.companyID
                                JOIN companyCategory ON
                                companyCategory.companyCategoryID=company.companyCategoryID
                                WHERE                         
                                MATCH(itemCategory.itemCategory) AGAINST(:searchKey3 IN BOOLEAN MODE) AND ST_Distance_Sphere( ST_GeomFromText(:userPoint3b), ST_GeomFromText(CONCAT('POINT(', company.companyLongitude, ' ', company.companyLatitude, ')'))) < 200000
                                )

                                UNION DISTINCT

                                (
                                select null,null,company.companyName as cm,company.companyID as companyID,companyCategory.companyCategory,ST_Distance_Sphere( ST_GeomFromText(:userPoint4a), ST_GeomFromText(CONCAT('POINT(', company.companyLongitude, ' ', company.companyLatitude, ')'))) as distance
                                From companyCategory
                                JOIN company ON
                                companyCategory.companyCategoryID=company.companyCategoryID
                                WHERE 
                                MATCH(companyCategory.companyCategory) AGAINST(:searchKey4 IN BOOLEAN MODE) AND ST_Distance_Sphere( ST_GeomFromText(:userPoint4b), ST_GeomFromText(CONCAT('POINT(', company.companyLongitude, ' ', company.companyLatitude, ')'))) < 200000
                                )
                        ) As allValue Group By allValue.companyID,allValue.cm,allValue.companyCategory,allValue.distance

Answer 1

The performance will probably be terrible. FULLTEXT shines when one MATCH in one table. It is not designed for what you are doing.

Plan A: Don't use FT for simple tests on what is usually a single word.

Plan B: Toss all the words into a column in a single table; then use single MATCH on that.

Further notes...

Sure, you might not have the exact name for a "company" or "item", but FT only helps with some fuzzy queries. LIKE "foo%" also helps only some, but might be better for your situation. (Note that does not have a leading wildcard.)

FT shines with long prose where you don't know where the desired keyword is.

For the UNION version, use UNION ALL unless you are expecting duplicates and need to avoid them.