CODEWITHSUNDEEP
mysqlsearchmultiple-columnskeyword
Nicox
Nicox

Reputation: 83

Best MySQL search query for multiple keywords in multiple columns

The problem here is that i have multiple columns:

| artist | name | lyrics | content

I want to search in these columns by multiple keywords. The problem is that i can't make any good algorithm with LIKE or/and. The best possibility is to search for each keyword in each column, but in that way i will get result that may contain the keyword in the name but will not contain the second keyword of artist. I want everything to be with AND, but this way, It will work for the keywords if there is only one column that i'm searching about. In other way, to receive a result, every of the column must have all keywords...

Is there any possibility someone to know what algorithm i have to create, that when you search with 3 keywords (ex: 1 for artist and 2 for name) to find the correct result?

Upvotes: 0

Views: 1525

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562681

The best solution is not to use MySQL for the search, but use a text-indexing tool like Apache Solr.

You could then run queries against Solr like:

name:"word" AND artist:"otherword"

It's pretty common to use Solr for indexing data even if you keep the original data in MySQL.

Not only would it give you the query flexibility you want, but it would run hundreds of times faster than using LIKE '%word%' queries on MySQL.

Another alternative is to use MySQL's builtin fulltext indexing feature: 

CREATE FULLTEXT INDEX myft ON mytable (artist, name, lyrics, content);

SELECT * FROM mytable 
WHERE MATCH(artist, name, lyrics, content) 
      AGAINST ('+word +otherword' IN BOOLEAN MODE)

But it's not as flexible if you want to search for words that occur in specific columns, unless you create a separate index on each column.

Upvotes: 3

fizzi
fizzi

Reputation: 116

AND works for displaying multiple rows too. it just depends upon the rows you have in your table which you havent provided. PS, im sorry if my answer is not clear, i dont have the reputation to make it a comment

Upvotes: 0

Related Questions