search in mysql

Question

how to do a mysql search for the following scenario?

search term example: great state

Mysql field,

• row 1 contains: great, city
• row 2 contains: awesome state
• row 3 contains: great state, and city

So, on search, over the given term, it shud give me all the three rows above.

what I want to achieve is, mysql shud search for the words in my search term, and if any word is found in the field, show it....

the mysql fields may contain a comma between words or may not contain.....

Also, cannot use full text feature, cause that requires the table engine to be myisam, and i cannot have myisam engine because i need to use foreign key relations on my tables

Answer 1

Try BOOLEAN FULL TEXT search http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

Answer 2

Your schema is not set up for text searching (implying that you're not an expert in this) but you say you require "foreign key relations on my tables" - which is either a very naive or very sophisticated requirement. I would suggest you think about which applies.

the mysql fields may contain a comma between words or may not contain.....

You're certainly going to have to build some sort of front end where you can at least normalise the parameters, I would recommend using a text search optimized schema, e.g.

base_data
---------
some_primary_key
some_text
some_other_stuff
...

base_data_words
---------------
bd_pk references base_data.some_primary_key
word
index(word)

then split up the words contained in base_data.some_text into base_data_words.word then finding stuff is simple and efficient.

C.