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


advertisement
 

Tip: Understanding FULLTEXT Searches

In MySQL, there is a provision to search based on natural mode. Follow this example to understand the process in detail.


advertisement

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 M S. is a Java developer from Bangalore, India. He holds a master's degree in Computer Science.
Email AuthorEmail Author
Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date