Sony VAIO VPC-CW21FX/P Laptop
Sony Ericsson T715 Slider 3G Phone in Rouge Pink
Nikon D3000 10.2MP Digital SLR Camera with 18-55mm f/3.5-5.6G AF-S DX VR Nikkor Zoom Lens
Motorola PEBL U6 Camera and Bluetooth
:: HOME MySQL Full Text Search
Full Text Search

Nowadays, most of people use LIKE to search in their database. It provides really cool results but in MySQL we have full-text search which provides better  solution to search in our database. But we have some limits to be able to use full-text search functions.



First of all, when we create database tables, we create them as  InnoDB which is better for update, insert and delete transaction. But to be able to use full-text search we need to create our tables as MyISAM which is better to use for select transaction but not so good to use for insert, update and delete transaction.


Moreover, we can just use full-text search function for CHAR, VARCHAR and Text columns. Also we need to define full-text search index column(s) during we create a table.


One more thing is to be able to get results, the keyword(s) you use to search should be equal to or less than %50 of current rows. For example, if you have 100 rows and you are searching "text" and if there are 51 rows containing "text", you will get zero result.


So as you see to be able to use full-text search function we have some constraints. The best way to use full-text search, we will create our database tables as InnoDB and all WEBsite will run on these tables. For searchable tables and fields we will create MyISAM tables but we don’t use them just-in-time transaction except search transaction. We will update our MyISAM tables once in a day at a current time which our WEBsite have the lowest visitor rate like 3 AM.

After all, We use MATCH () AGAINST() syntax to search keywords.


SELECT * FROM table WHERE MATCH(FieldName) AGAINST ('Keywords')


You don’t need to do anything for comma or spaces. Full-Text search handles all and gives you results in order the search relevance. FieldName should be index full-text search field. And What is search relevance is  if you search "text" keyword and in the first row there are 3 text keyword and at the second row there are 5 text keyword. So as a result, you will see second row as a first and then first row as a second result.


You can also get search relevance as a result. For that you just need to MATCH() AGAINST at SELECT part of query.


SELECT MATCH(FieldName) AGAINST ('keywords') as Relevance FROM Table WHERE MATCH(FieldName) AGAINST('keywords').


As you see, it is very useful but you should know how to use it. Lets try to understand it better with using an example. You have blog WEBsite and you keep your blogs, blog comments, categories and logs/hits of your blogs. These table will be InnoDB because you have update, insert and delete transactions. You want to search just blog title, blog content and comment content fields with our search form. So you need to create MyISAM table for these fields and define them as index for full-text search. If you use PHPmyadmin to create table, at the end of row, you will see capital T to define full-text search field. Then you need to create Cron Jobs to update these fields at 3 AM or when ever you want. Because of that, at the result of search, you don’t show up very current comments and blogs in last 24 hours.


If you used LIKE to search, You would need to separate comma or spaces and you need to check one by one each field and also you need to write some more complex query to order them. But at full-text search you just need to write one simple query and it will do everything for you.