1

I'm using MySql 8. I would like to speed up searches on my table of the form

select * FROM dirctory_coop where name like '%mystr%';

and someone mentioned using Full text searches -- https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html. However, I'm having an issue with this. Below is my table ...

mysql> desc directory_coop;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int          | NO   | PRI | NULL    | auto_increment |
| name     | varchar(250) | NO   |     | NULL    |                |
| enabled  | tinyint(1)   | NO   |     | NULL    |                |
| phone_id | int          | YES  | MUL | NULL    |                |
| email_id | int          | YES  | MUL | NULL    |                |
| web_site | longtext     | NO   |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+

I tried the below statements to update my column definition but am getting the following errors ...

mysql> alter table directory_coop modify column name FULLTEXT;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FULLTEXT' at line 1
mysql> alter table directory_coop modify name FULLTEXT;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FULLTEXT' at line 1

What else do I need to do to enable the performance optimizations that full text search supposedly provides?

2 Answers 2

0

You have to add or create an INDEx like

 ALTER TABLE directory_coop ADD FULLTEXT idx_name(name);

For more information read the manual ALTER TABLE

0

The first thing you must do is to create a FULLTEXT index as follows:

ALTER TABLE directory_coop ADD FULLTEXT name_ftndx (name);

That's just the beginning.

The next thing you need to do is understand the SQL syntax for using FULLTEXT indexes.

Please read the MySQL Documentation on FULLTEXT Indexes

I have many posts in the DBA StackExchange about using them.

Here is a good example to start you off : Why full-text-search returns less rows than LIKE. This will show you the difference between using LIKE and using the MATCH function.

I still recommend reading the MySQL Docs before reading my posts.

CAVEAT

Please know that 36 Words are not included in an InnoDB FULLTEXT Index.

I have mentioned the twice in the DBA StackExchange

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.