Menu
I'm not getting any rows returned with the following, and I don't know why. Have I defined the fulltext index correctly?
CREATE TABLE `client_contact` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`first_name` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`last_name` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`email` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`phone` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
`company` int(11) DEFAULT NULL,
`billing_address` text COLLATE utf8_unicode_ci,
PRIMARY KEY (`id`),
FULLTEXT KEY `client_search` (`first_name`,`last_name`,`email`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
INSERT INTO `client_contact` (`first_name`, `last_name`, `email`, `phone`, `company`, `billing_address`) VALUES
('John', 'Smith', '[email protected]', '123456', 1, '1 Any Street'),
('Mary', 'Smith', '[email protected]', '123456', 1, '1 Any Street');
SELECT cl.*
FROM client_contact cl
WHERE MATCH(cl.first_name, cl.last_name, cl.email) AGAINST ('Smith')
SQL Fiddle here
291
Full-text indexes are created on text-based columns ( CHAR , VARCHAR , or TEXT columns) to speed up queries and DML operations on data contained within those columns. A full-text index is defined as part of a CREATE TABLE statement or added to an existing table using ALTER TABLE or CREATE INDEX .
The basic query format of full-text searches in MySQL should be similar to the following: SELECT * FROM table WHERE MATCH(column) AGAINST(“string” IN NATURAL LANGUAGE MODE); When MATCH() is used together with a WHERE clause, the rows are automatically sorted by the highest relevance first.
To drop a FULLTEXT index, you use the ALTER TABLE DROP INDEX statement. In this tutorial, you have shown you how to create FULLTEXT indexes that support full-text search in MySQL.
MySQL has support for full-text indexing and searching: A full-text index in MySQL is an index of type FULLTEXT . Full-text indexes can be used only with InnoDB or MyISAM tables, and can be created only for CHAR , VARCHAR , or TEXT columns.
This is because the keyword Smith exists in all rows. MySQL manual says "Words that are present in 50% or more of the rows are considered common and do not match".
138
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
