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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
