Mysql – How to optimise fulltext search on different table with different field in thesql

full-text-searchMySQL

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

Best Answer

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.

Using MySQL Query Expansion

Summary: in this tutorial, you will learn about MySQL query expansion to widen the search results based on automatic relevance feedback.

Introduction to MySQL Query Expansion

In some cases, users want to search for information that based on the knowledge that they have. Users use their knowledge to define keywords to search for information, and typically those keywords are too short. To help users to find what they want based on the too-short keywords, MySQL full-text search engine introduces a concept called query expansion.

The query expansion is used to widen the search result of the full-text searches based on automatic relevance feedback (or blind query expansion). Technically, MySQL full-text search engine performs the following steps when the query expansion is used:

First, MySQL full-text search engine looks for all rows that match the search query.
Second, it checks all rows in the search result and finds the relevant words.
Third, it performs a search again but based on the relevant words instead the original keywords provided by the users.

From application perspective, you can use the query expansion when the search results are too few. You perform the searches again but with query expansion to offer user more information that are related and relevant to what they are looking for.

To use the query expansion, you use the WITH QUERY EXPANSION search modifier in the AGAINST() function. The following illustrates the syntax of the query using the WITH QUERY EXPANSION search modifier.

SELECT column1, column2
FROM table1
WHERE MATCH(column1,column2)
      AGAINST('keyword',WITH QUERY EXPANSION)

MySQL Query Expansion Example

Let’s take a look at an example to see how MySQL query expansion works.

We will use the productName column of the products table to demonstrate the query expansion feature. First, we enable the full-text search feature for this column.

ALTER TABLE products
ADD FULLTEXT(productName)

Second, we search for product whose product name contains the 1992 keyword without using query expansion.

SELECT productName
FROM products
WHERE MATCH(productName) AGAINST('1992')

Gives

+-----------------------------------+
| productName                       |
+-----------------------------------+
| 1992 Ferrari 360 Spider red       |
| 1992 Porsche Cayenne Turbo Silver |
+-----------------------------------+
2 rows in set (0.00 sec)

As you see, the search results has 2 products whose product name contains 1992.

Third, we can widen the search result by using query expansion as the following statement:

SELECT productName
FROM products
WHERE MATCH(productName)
      AGAINST('1992' WITH QUERY EXPANSION)

Gives

+-------------------------------------+
| productName                         |
+-------------------------------------+
| 1992 Porsche Cayenne Turbo Silver   |
| 1992 Ferrari 360 Spider red         |
| 2001 Ferrari Enzo                   |
| 1932 Alfa Romeo 8C2300 Spider Sport |
| 1948 Porsche 356-A Roadster         |
| 1948 Porsche Type 356 Roadster      |
| 1956 Porsche 356A Coupe             |
+-------------------------------------+
7 rows in set (0.00 sec)

We got more rows in the search result when we used query expansion. The first two rows are the most relevant and the other rows come from the relevant keyword that are in the first two rows e.g., Ferrari.

Notice that blind query expansion tends to increase noise significantly by returning non-relevant results. It is highly recommended that you use query expansion only when the searched keyword is short.

In this tutorial, we have introduced you to MySQL query expansion to widen the search results when the keywords provided by users are short.

Source: Using MySQL Query Expansion

Mysql – Calculate MATCH() AGAINST() scores from UNIFIED QUERY not FOR EACH TABLE

Perhaps you could record the following from the three(3) tables

table name
the column from the table name
FULLTEXT index on the column

Here is the code:

DROP TABLE IF EXISTS combined_data;
CREATE TABLE combined_data
(
    source_table VARCHAR(64),
    source_id INT NOT NULL,
    data TEXT NOT NULL,
    FULLTEXT (data)
) ENGINE=MyISAM;
--
ALTER TABLE combined_data DISABLE KEYS;
--
INSERT INTO combined_data (source_table,source_id,data) VALUES
SELECT 'table1',id,`result` FROM table1 WHERE MATCH(`result`) AGAINST('keyword');
--
INSERT INTO combined_data (source_table,source_id,data) VALUES
SELECT 'table2',id,`content` FROM table1 WHERE MATCH(`content`) AGAINST('keyword');
--
INSERT INTO combined_data (source_table,source_id,data) VALUES
SELECT 'table3',id,`text` FROM table1 WHERE MATCH(`text`) AGAINST('keyword');
--
ALTER TABLE combined_data ENABLE KEYS;

Now you can run a single query against the one table

SELECT *,MATCH(`data`) AGAINST('keyword') as `good_score`
FROM combined_data
ORDER BY `good_score` DESC;

Give it a Try !!!

Best Answer

Related Solutions

Mysql – WITH QUERY EXPANSION MODE in MySQL Fulltext Search

Using MySQL Query Expansion

Introduction to MySQL Query Expansion

MySQL Query Expansion Example

Mysql – Calculate MATCH() AGAINST() scores from UNIFIED QUERY not FOR EACH TABLE

Related Question