CODEWITHSUNDEEP
phpmysqlfull-text-search
Boardy
Boardy

Reputation: 36205

Searching for keywords in mysql database most efficiently

I am working on a project where I have a database, that contains a summary field, which is filled in by a web form that visitors to the site enter on.

When the user completes entering the summary field, I want to perform a lookup using the words that were entered by the the user on the page for similar records in the database that contain the same keywords that they've filled in on the page.

I was thinking I could split the summary string that is submitted and then loop through the array and build up a query so the query would end up something like:

SELECT * 
FROM my_table 
WHERE summary LIKE '%keyword1%' 
OR summary LIKE '%keyword2' 
OR summary LIKE '%keyword3%';

However, this seems massively inefficient, and as the database could grow quite big, could potentially become quite a slow query to run.

I then found the MySQL IN clause, but this only seems to work with multiple values where a field can only contain 1 of these values in a row.

Is there a way I can use the IN function, or is there a better MySQL function that I can use to do what I want, or is my first idea the only way round it?

An example of what I am trying to achieve is a bit like on Stack Overflow. When you lose focus of the title field, it pops up similar questions based on the title you've provided.

Upvotes: 0

Views: 59

Answers (1)

Drew
Drew

Reputation: 24959

I would recommend reading this manual page InnoDB FULLTEXT Indexes and the one on Full-Text Restrictions. New functionality of full text has been incorporated in recent releases of mysql, augmenting the use of it with INNODB tables.

Concerning the inability to upgrade a mysql version, there is no reason why one cannot mix and match MyISAM and INNODB tables in the same db. As such, one would keep textual information in MyISAM (where historically FTS index power was available), and doing joins to INNODB tables when needed. This avoids the "must upgrade to version 5.6" argument.

Legend: FTS=Full Text Search

Upvotes: 1

Related Questions