From: Shawn Green
Date: August 11 2012 4:41pm
Subject: Re: search any matching word in given string in table column which
 has only one word
List-Archive: http://lists.mysql.com/mysql/227985
Message-Id: <50268B25.8030104@oracle.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 8/11/2012 12:05 PM, Rajeev Prasad wrote:
> here is given string of type:...   "as23,rt54,yh79"
> and i have to lookup in a table which has column "id" which has only one four letter word. i have to select that row in which the colum 'id' value matches any of the word in given string...
> ... snipped ...

Hello Rajeev,

Relational database theory has been shown to be quite effective at 
solving problems like yours.  The problem with your design is that you 
do not have the ability to do an exact-value index for every 4-letter 
set in your longer strings. The relational solution is to make two 
tables. One for the row and one for the list of values that belong to 
that row.

Another approach to this is to look at your lists of values as 4-letter 
words. For this design techniques of full-text indexing may be 
applicable. However many full-text engines will omit or ignore words 
that appear too often in the data as they have very low selectivity. For 
example, if you had a database of news articles and each row contained 
the contents of one article, then the most common words like "a", "an", 
"the", "like", "with", "on", and so forth are very likely to appear in 
every single row of data. This makes those terms useless as search 
items.  If you happen to have one of your 4-letter words of data that 
manages to appear in enough rows to cross that threshold, then using a 
full-text index will fail to locate any rows that contain that value.

My preference is to use the two-table approach as I can index both the 
descriptive data (on the parent table) and all of the values that appear 
in the list (on the child table) to make retrieval both accurate and 
very fast.

Regards,
-- 
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN