devxlogo

Understanding FULLTEXT Searches

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’.

devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist