When we normally have an application associated with a MySQL database and we want to carry out a search, we would normally go for the yucky “LIKE%” syntax when writing our search query. What if I told you that there is a much better indexed method built right in to MySQL that you could use to run an optimised and effective search? Welcome to the world of MySQL Fulltext searching. Today we’ll discuss how to define Fulltext indices on a MySQL database table and how to run a simple search query on them.
First things first, let’s install PhpMyAdmin. I’m going to assume that you already have MySQL installed. PhpMyAdmin is a handy tool that lets us manage our MySQL databases without having to go through the hassle of typing out long terminal commands. You can use any other similar GUI tool such as MySQL Workbench as well.
Now, I have a database called etutor where I have a table called questions. In order to set up Fulltext indices on this table, I’m going to have to convert it to a MyISAM-engine-based table. This can be achieved by selecting the table and going to the ‘Operations’ tab:
When I open up the structure of the questions table in PhpMyAdmin, there’s a small ‘fulltext’ icon on the right of each attribute of the table.
This is the button with the little ‘T’ on it. Clicking on this will define that attribute (it needs to be VARCHAR or Text) type as a Fulltext index, which we can search on.
Expanding the ‘Details’ section at the bottom of the page will show you all the Fulltext indices you’ve defined on your table.
Its that simple to define a Fulltext index on your database table! Now let’s move on to some searching.
My search query will utilise the ‘MATCH’ and ‘AGAINST’ keywords in MySQL to search through my Fulltext indices:
SELECT * FROM questions WHERE MATCH(subject) AGAINST('SEARCH_QUERY') OR MATCH(tags) AGAINST('SEARCH_QUERY');
And frankly, it’s that simple to search through those pesky text indices too. You will find that there is a massive performance boost when using Fulltext search vs. the traditional ‘LIKE%’ query approach.