[RESOLVED] Creating a Search (of a blob)

StevenJ

Hi,

I have a field in my MySQL database, and in this I enter a desription of the record.

This field, is imaginatively called 'description'.

Now, I am adding a search function to the application, and I wish to search this field.

While I can usually create a normal search, what I have done doesn't wok, and it appears to be becuase the field 'decription' is a blob.

I choose to use a blob because I don't wish to have a restriction on the number of characted entered in the description, or I wanted more than the 255 available with text.

What is the best way around this? can you search a blob? or am I best changing the type of field to something more appropriate?

thankx. StevenJ

Roger_Ramjet

Yes it's because it's a blob

"BLOB columns have no character set, and sorting and comparison are based on the numeric values of the bytes in column values."

You should change the type to one of the text types. While you are at it consider using full text search indexing on the column as well.

StevenJ

Roger Ramjet wrote:
While you are at it consider using full text search indexing on the column as well.

thanks for that. I have actually changed it to "longtext" so I could carry on.

What do you mean by the above? Please?

Roger_Ramjet

You should read about Full Text Search . It will be faster to search than using %like% etc, as well as ordering the results more usefully.

StevenJ

That is what I have been looking for!

thanks!

I knew there was a better way! I had come across that, but have never needed to implement anything like this before.

cheers again.

StevenJ

Something is not right.

I have added indexes (FULLTEXT) to the two fields I wish to search, these are
image_name and description

I have 4 records in the DB for testing

in one I have 57309 and in another I have 57308 and another 87028

When I use the SQL:
SELECT * FROM es_img_images where match(image_name, description) against ('87028') AND active='Y'

I get no results.

when I try SELECT * FROM es_img_images where match(image_name, description) against ('57308') AND active='Y'
i do get a result

and when I try

SELECT * FROM es_img_images where match(image_name, description) against ('57309') AND active='Y'

I get no results.

Why is there an inconsistency?

Roger_Ramjet

People often run into this problem when testing fts. It is because you do not have enough data in the table to test. By Default, if a word occurs in 50% of the rows then it is discarded from the match.

" A word that matches half of the rows in a table is less likely to locate relevant documents. In fact, it most likely finds plenty of irrelevant documents. We all know this happens far too often when we are trying to find something on the Internet with a search engine. It is with this reasoning that rows containing the word are assigned a low semantic value for the particular dataset in which they occur. A given word may exceed the 50% threshold in one dataset but not another.

The 50% threshold has a significant implication when you first try full-text searching to see how it works: If you create a table and insert only one or two rows of text into it, every word in the text occurs in at least 50% of the rows. As a result, no search returns any results. Be sure to insert at least three rows, and preferably many more. Users who need to bypass the 50% limitation can use the boolean search mode; see Section 12.7.1, “Boolean Full-Text Searches”. "

All you have to do is to insert a realistic number of records and you will be able to test to your heart's content. Or you could use the boolean mode to overcome it.

StevenJ

Hi RR,

thanks again for the very helpful reply.

This is a lesson to myself, to understand something before using it!

I added a few more records, and edited a few. Low and behold, as per your explaination, I got better results.

The only correlation I could make was that I had edited the 'description' field which was previously as a BLOB, and this had somehown changed the properties of the data. I thought that maybe it needed editing to change the data from binary? There was no science behind this just the fact that initially the searches didn't work.

I have added more records, and do get the results I expect.

While I think that this method is excellent, what happens if a key word is genuinely used in more than 50% of the records?

thanks again RR.

StevenJ

Roger_Ramjet

Well as I said you can override that with the boolean mode search.