Understanding FULLTEXT Searches

In MySQL, there is a provision to search based on natural mode. Let us consider the following example to understand this in detail.

Creation of the table:

CREATE TABLE 'PROP_TBL' (
	'PROPERTY' VARCHAR(100),
	'DOMAIN' VARCHAR(20),
	'VALUE' VARCHAR(2000),
	'PATH' VARCHAR(100),
	PRIMARY KEY ('PROPERTY', 'DOMAIN', 'PATH'),
	FULLTEXT (PROPERTY,DOMAIN)
)
ENGINE=InnoDB
; 

And use the following insert statements,

INSERT INTO 'PROP_TBL' ('PROPERTY', 'DOMAIN', 'VALUE', 'PATH') VALUES ('CLEANUP MAX SESSION TIMEOUT', 'ALARMS', '1800000', '/MICROWAVE/TSSH');
INSERT INTO 'PROP_TBL' ('PROPERTY', 'DOMAIN', 'VALUE', 'PATH') VALUES ('ALARM DATA PRIORITY', 'ALARMS', 'false', '/MICROWAVE/GLOBAL');
INSERT INTO 'PROP_TBL' ('PROPERTY', 'DOMAIN', 'VALUE', 'PATH') VALUES ('CN FORB EQ LIST', 'ALARMS', ',4,5,6,7,11,', '/MICROWAVE/GLOBAL');
INSERT INTO 'PROP_TBL' ('PROPERTY', 'DOMAIN', 'VALUE', 'PATH') VALUES ('COMMAND EXECUTION BLOCK TIME', 'ALARMS', '1000', '/MICROWAVE/TSSH');
INSERT INTO 'PROP_TBL' ('PROPERTY', 'DOMAIN', 'VALUE', 'PATH') VALUES ('COMMAND EXECUTION SUFFIX', 'ALARMS', '', '/MICROWAVE/GLOBAL');
INSERT INTO 'PROP_TBL' ('PROPERTY', 'DOMAIN', 'VALUE', 'PATH') VALUES ('COMMAND REPUSH TIME', 'ALARMS', '3', '/MICROWAVE/GLOBAL');
INSERT INTO 'PROP_TBL' ('PROPERTY', 'DOMAIN', 'VALUE', 'PATH') VALUES ('COMMAND REPUSH TIMEOUT', 'ALARMS', '60',  '/MICROWAVE/GLOBAL');
INSERT INTO 'PROP_TBL' ('PROPERTY', 'DOMAIN', 'VALUE', 'PATH') VALUES ('COMMAND REPUSH TO NE', 'ALARMS', 'true', '/MICROWAVE/GLOBAL');
INSERT INTO 'PROP_TBL' ('PROPERTY', 'DOMAIN', 'VALUE', 'PATH') VALUES ('COMMAND REPUSH WAIT TIME BTW CMDS', 'ALARMS', '60', '/MICROWAVE/GLOBAL');
INSERT INTO 'PROP_TBL' ('PROPERTY', 'DOMAIN', 'VALUE', 'PATH') VALUES ('COMMAND RETRY COUNT', 'ALARMS', '3', '/MICROWAVE/TSSH');
INSERT INTO 'PROP_TBL' ('PROPERTY', 'DOMAIN', 'VALUE', 'PATH') VALUES ('COMMON RETRY COUNT', 'ALARMS', '3', '/MICROWAVE/GLOBAL');
INSERT INTO 'PROP_TBL' ('PROPERTY', 'DOMAIN', 'VALUE', 'PATH') VALUES ('COMMON RETRY TIMOUT PERIOD', 'ALARMS', '60', '/MICROWAVE/GLOBAL'); 

Here, note that we have used FULLTEXT (PROPERTY,DOMAIN) along with the table creation, which indicates that there can be a search on the columns involved in the FULLTEXT.

Querying the table as below, we have

Query:

SELECT * FROM PROP_TBL WHERE MATCH (PROPERTY,DOMAIN) AGAINST ('commanD' IN NATURAL LANGUAGE MODE); 

Output

+-----------------------------------+--------+--------------+-------------------+
| PROPERTY  						| DOMAIN | VALUE 		| PATH 				|
+-----------------------------------+--------+--------------+-------------------+
| COMMAND EXECUTION BLOCK TIME		| ALARMS |	2500		| /MICROWAVE/TSSH	|
| COMMAND EXECUTION SUFFIX			| ALARMS |	300			| /MICROWAVE/GLOBAL	|
| COMMAND REPUSH TIME				| ALARMS |	35			| /MICROWAVE/GLOBAL	|
| COMMAND REPUSH TIMEOUT			| ALARMS |	60			| /MICROWAVE/GLOBAL	|
| COMMAND REPUSH TO NE				| ALARMS |	true		| /MICROWAVE/GLOBAL	|
| COMMAND REPUSH WT TIME BTW CMDS	| ALARMS |	50			| /MICROWAVE/GLOBAL	|
| COMMAND RETRY COUNT				| ALARMS |	3			| /MICROWAVE/TSSH	|
+-----------------------------------+--------+--------------+-------------------+

The output can vary based on the search text in the place of 'commanD'.