dcsimg
LinkedIn
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL
Expertise: Intermediate
Dec 31, 2019

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

Sridhar MS
 
Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date