选择列文本包含数组值的行

Question

I'm building a search function in php/mysql and I'm looking for the right MySql function. My table sort of looks like this:

id    | text
-------------------------------------- 
1     | I like pony's.
2     | Do you like fish?
3     | We like fishes!

I want to search the column 'text' for one of the exact values of an array, for example:

$search_array = array('fish','dogs','cat','panda');

I'm looking for the right MySql function to return only the second row (with the current array). The array can contain hundreds of values.

I have 6000+ rows, growing everyday with +/- 400. I've tried REGEXP but with a large array, it took about 10 seconds before it returned the corresponding rows.

Please help, I'm fighting with this for almost 3 full days now... Thanks in advance!

Answer 1

If the search array is constant, or changes infrequently, I recommend having another two tables, 'tags' and 'tags-text'.

For example, the row with id 2 in your example contains fish, since fish is in our 'tags' table a new record will be placed in a 'tags-text' table. When you are searching with your array, you can search if one of the array components is in the 'tags-text' table, and join the 'text' table and return the text and id and do whatever you need.

Structure of other tables:

'tags' table

id    | tags
-------------------------------------- 
1     | fish
2     | dogs
3     | cats

'tags-text' table

text-id  | tags-id
-------------------------------------- 
2        | 1

Does this help/make sense