FULL TEXT Mysql 5.6

mysql-5.6-full-text.sh

# TABLE
DROP TABLE IF EXISTS `searchs`;
CREATE TABLE `searchs` (
  `id_search` int(11) NOT NULL AUTO_INCREMENT,
  `username_user` varchar(45) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `services_profile` text,
  `gender_profile` varchar(1) DEFAULT NULL,
  `id_user` int(11) NOT NULL,
  PRIMARY KEY (`id_search`,`id_user`),
  UNIQUE KEY `fk_searchs_users1_idx` (`id_user`),
  FULLTEXT KEY `full` (`name`,`services_profile`,`username_user`),
  CONSTRAINT `fk_searchs_users1` FOREIGN KEY (`id_user`) REFERENCES `users` (`id_user`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=879 DEFAULT CHARSET=utf8;

# DATA
INSERT INTO `searchs` VALUES (1,'maryam.schroeder','Mr. Buford Cronin IV Bode','consequuntur	iste	repudiandae	sequi	vitae','M',1),(2,'nels68','Dawson Barrows Mayert','ad','M',2),(3,'schaefer.marion','Albin Gusikowski Rohan','aperiam	dolor	eaque	harum','M',3),(4,'dtremblay','Prof. Brenden Hamill Jr. Parisian','libero	quia	repellendus','M',4),(5,'jeff91','Mr. Bill Jones Steuber','non','M',5);

# SQL
SELECT *FROM searchs WHERE MATCH(username_user,name,services_profile) AGAINST('Jones' IN BOOLEAN MODE);


####
SELECT * FROM asistemas.searchs;

-- 01 FULL TEXT  Boolean
SELECT *FROM searchs 
WHERE MATCH(name,services_profile,username_user) 
AGAINST('+Jones+ Jones*' IN BOOLEAN MODE); -- '+Jones+ Jones*'

-- RELEVANCE
SELECT searchs.*, match(name,services_profile,username_user) AGAINST ('Jone*' IN BOOLEAN MODE) AS relevanceScore
FROM searchs
WHERE match(username_user,name,services_profile) AGAINST ('*Jones*' IN BOOLEAN MODE)
ORDER BY relevanceScore DESC;

-- 02 FULL TEXT  NATURAL AND EXPANSIVE

SELECT * FROM searchs
WHERE MATCH (name,services_profile,username_user)
AGAINST ('jon eichmann' IN NATURAL LANGUAGE MODE);

-- 
SELECT * FROM searchs
WHERE MATCH (name,services_profile,username_user)
AGAINST ('jon eichmann' WITH QUERY EXPANSION);
--
SELECT * FROM searchs
WHERE MATCH (name,services_profile,username_user)
AGAINST ('jon eichmann' IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
);


#################################################
#################################################
SELECT * FROM asistemas.searchs;

-- 01 FULL TEXT  Boolean
SELECT *FROM searchs 
WHERE MATCH(name,services_profile,username_user) 
AGAINST('+Jones+ Jones*' IN BOOLEAN MODE); -- '+Jones+ Jones*'

-- RELEVANCE
SELECT searchs.*, match(name,services_profile,username_user) AGAINST ('Jone*' IN BOOLEAN MODE) AS relevanceScore
FROM searchs
WHERE match(username_user,name,services_profile) AGAINST ('*Jones*' IN BOOLEAN MODE)
ORDER BY relevanceScore DESC;

-- 02 FULL TEXT  NATURAL AND EXPANSIVE

SELECT * FROM searchs
WHERE MATCH (name,services_profile,username_user)
AGAINST ('jon eichmann' IN NATURAL LANGUAGE MODE);

-- 
SELECT * FROM searchs
WHERE MATCH (name,services_profile,username_user)
AGAINST ('jon eichmann' WITH QUERY EXPANSION);
--
SELECT * FROM searchs
WHERE MATCH (name,services_profile,username_user)
AGAINST ('jon eichmann' IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
);