0

I am trying to search a table for multiple keywords. However, I am not looking for one keyword or even 10. It is around one thousand keywords. These keywords are also in a table and can be controlled. I would rather not hard-code these keywords into my SQL command...

The target table I am searching contains a lot of text and a cell could contain an entire sentences or paragraph... so doing something like a 'full text' search in mySQL seems like a good start.

Very similar to this question, but again, when I speak of multiple keywords, I mean hundreds to thousands. mysql FULLTEXT search multiple words

Can I dump my keyword table into an array and run a FULLTEXT search? Can this even be approached with mySQL or are there limits im not considering? Im open to other technology suggestions too. Sorry that I dont have code or errors to post. I am first trying to understand conceptually how to approach this. -tia

Community
  • 1
  • 1
nodoze
  • 127
  • 1
  • 14

4 Answers4

0

Recently i had to do a similar decision. I decided to go with lucene. I store the indexable fields in lucene, and return an id for mysql row.

Other choice is sphinx , a full tutorial can be found here.

See a related post here. And here.

Community
  • 1
  • 1
etr
  • 1,252
  • 2
  • 8
  • 15
  • Thanks for the tip. I'll read up on this, but at first glance, I see no reference in those posts to searching for multiple keywords that are stored in a table. Although these links seem to address scaling issues, most of it is still regarding searching for a single keyword/phrase/string. – nodoze Jul 12 '14 at 03:21
0

SELECT * FROM articles WHERE body LIKE '%$keyword%';

Thamothara Kannan
  • 1
  • and %$keyword% can be an entire table of values? or at a minimum, an entire column? please explain. sorry for the lack of understanding. – nodoze Jul 12 '14 at 08:02
0

you just need to use a for loop in your mysql query to read all keyworks.

its clear that you separated your keywords with comma or -

so you need to explode keyword by comma and put them in a variable .

for example :

  $keywords =  "key1,key2,key3,...";  // values come from keywords column from db.

now you just need to explode $keywords.

  $keys=explode(',',$keywords);

and finally in your query you need to use a for loop:

  $query = "SELECT * From targettable";
            $i = 0;
            foreach ($keys as $key)
             {
            $query .= "WHERE keywords LIKE '%".$key."%' )";
            $i++;     
             }

i named your keywords column = "keywords".

also you can easily add your other condition into $query.

Bahrami-Reza
  • 608
  • 2
  • 7
  • 24
  • Thanks! Do you think this will be viable in the long run for performance and scalability? (roughly two thousand keywords) I will start testing this today and see how it goes. – nodoze Jul 12 '14 at 14:57
  • is there a better way to approach this than hard coding all of my keywords into the query? We will be slowly adding keywords to the table. – nodoze Jul 13 '14 at 06:57
  • if your tables are using MyISAM you can query with FULLTEXT but im not sure about performance. just check this once : [link](www.websanova.com/blog/mysql/mysql-full-text-search#.U8Ivlaitv-Z) – Bahrami-Reza Jul 13 '14 at 07:07
0

Watch out with mysql fulltext searches, if your result set is > 50% of the entire data set, mysql sees this is a failed search, and you wont get anything.

This sounds likely to occur if you have a hit list of 1000s of possible words.

I'd say you would be better off investigating the extraction of keywords from text (lets imagine they are articles) as they are stored.

Then store these keywords in their own table.

For best results though, you'd likely want to investigate Natural Language Processing and extract meaning from articles rather than just words.

Cups
  • 6,901
  • 3
  • 26
  • 30