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?