A few days ago, I wrote an article on how to add a search feature in CodeIgniter projects. The method I used was “Pattern Matching” using SQL’s LIKE statement. Although the LIKE statement works great, it is slow. The search will crawl in situations where there are millions of rows in a table.
An alternative method is using a FULLTEXT index search. It uses SQL’s MATCH AGAINST. To start using a FULLTEXT search, a database table will need to be indexed first. You can accomplish this by altering a table and adding FULLTEXT indexing to certain fields:
ALTER TABLE books ADD FULLTEXT(bookname,author,characters,synopsis);
Once a table has been indexed, you can start using MATCH AGAINST.
I tried using CodeIgniter’s active record and MATCH AGAINST, but I could not get them to work. So, I ended up writing out the SQL statement using $this->db->query.
$match = $this->input->post('search'); $searchcount = $this->db->query("SELECT * FROM books WHERE MATCH (bookname,author,characters,synopsis) AGAINST ('.$match.')");
So, if you want to increase search performance, consider using a FULLTEXT search.