Indexing and querying Sybase

Classic Servoy
1

I have a table…a big table…with 2 columns: id(pk) and description (text datatype). The table contains 50.000 rows and the description column contains each about 100 kB of text.
Basically I want to do searches a la Filemaker on the description column. For example: ‘red house’ would find ‘red house’ and ‘house red’ and ‘houses are red’ and ‘red houses’

Now I have indexed the description column ( I even tried clustered indexing).
Searches with:
WHERE description LIKE ‘blah%’ are no problem, but that isn’t suitable for the searches
WHERE description LIKE ‘%blah%’ are a huge problem, the query goes on and on…it takes so much time that I everytime quit after 5 or more minutes…(I tried to measure the time and it took about between 45 to 60 minutes)

I think if the query would look like:
WHERE description LIKE ‘%red%’ OR description LIKE ‘%house%’
It would take forever

Are there any other queries I can do that I dont know about, can I do stuff to the table to make the search acceptable??
Any suggestions…is there something like word-indexing like in Progress or am I just pushing/crossing over the limits here?

2

Hi Tweetie

I have a documents table that currently holds @ 53,000 emails, letters & Faxes - about 42mb of data. A full text search on the [detail] field takes just 16-20secs. Searching on the [subject] field or even [company] through a relationship is usually less than 3secs.

Have copied the standard Servoy code below in case it helps.

controller.find()
if ( globals.doc_search_detail01 )
{ detail = ‘%’ + utils.stringTrim(globals.doc_search_detail01) + ‘%’ }

if ( globals.doc_search_subject )
{ subject = ‘%’ + utils.stringTrim(globals.doc_search_subject) + ‘%’ }

if ( globals.doc_search_company )
{ documents_to_contacts.companyname = ‘%’ + utils.stringTrim(globals.doc_search_company) + ‘%’ }

controller.search()

Handles multi word searches like “231 seed tray” in about same time as single word. I was going to test if using SQL Queries would be quicker but maybe the Servoy code is just fine and I should leave well alone. Using Sybase db and Servoy 223.

HTH

Graham Greensall
Worxinfo Ltd

3

grahamg:
Hi Tweetie

I have a documents table that currently holds @ 53,000 emails, letters & Faxes - about 42mb of data. A full text search on the [detail] field takes just 16-20secs. Searching on the [subject] field or even [company] through a relationship is usually less than 3secs.

How big are the columns…my column was quite large, about 100.000 characters

4

That could be the answer - my [details] column is set to 10,000chars.

Are you using all the 100,000 chars? If not it might be worth seeing if MySQL VARCHAR (max 65,535 from 5.0.3) or TEXT handles your text searching any better. In next few days will be testing my system on MySQL and will let you know if any significant differences in search times.

Graham Greensall
Worxinfo Ltd

5

grahamg:
That could be the answer - my [details] column is set to 10,000chars.

Are you using all the 100,000 chars? If not it might be worth seeing if MySQL VARCHAR (max 65,535 from 5.0.3) or TEXT handles your text searching any better. In next few days will be testing my system on MySQL and will let you know if any significant differences in search times.

Graham Greensall
Worxinfo Ltd

In my testdatabase I was using all the 100.000 character for testing purposes