Leading wildcard used with LIKE causing table scan / timeout

[manjoufna]

Hello,

I'm trying to use an SQL query to search a table using LIKE with a leading wildcard (%) on 2 name fields defined as char(30).

Like this:

SELECT name1, name2 FROM customers
WHERE name1 like "%SALLY%"
OR name2 like "%SALLY%"


This query keeps timing out. As I understand it, if you use leading wildcards, it automatically does a table scan regardless of whether or not the 2 fields are part of an index. This table has nearly 500,000 rows. I asked our Sybase DBA (I'm a developer, so I'm not a Sybase expert) if we could get a full-text search option enabled for this to improve the response time.

He told me that a full-text search option only works on text fields.

I asked if we could redefine our two fields as text, so that we could use the full-text search.

I didn't completely follow his response, so I will quote it here:

"Each text field is a minimum of 2K page and its an extra I/O because it stored away from row. You would always have to rewrite every query from every application which uses these tables. I don't think this is a feasible solution"

In other words, no. He suggested we not use a leading wildcard, that the users entering the search criteria should know the first few characters of these name fields. I disagree.

I'm running out of ideas and we need to be able to give the user the ability to search on these two names fields. For example, if they enter "SALLY", we have to locate the records whose 2 name fields contain:
SALLY PARKER
or
SUSAN SALLY PARKER
or
etc.

It's possible we could insist on redefining the fields to text if necessary (I don't know who would win), but is there another way to do this?

Thanks.

p.s. We're using Sybase 12.0 and 12.5 that run on Unix boxes (Solaris), if that helps.

 

[tinkuK]

Hi
You try with the following query

SELECT name1, name2 FROM customers
WHERE charindex("SALLY",name1+" "+name2)!=0

I think it will sreve your purpose

Gopal

 

[micha123]

Dear tinkuk,
Are you sure this would not issue a TABLE SCAN?


"A long life is when each day is full and every hour wide!"
(A Jewish Mentor)
M.