sql - I need to implement wildcard search using % on both ends, how can I improve performance?

Question

I will be implementing this in the sql side, so I would end up having something like

where company.name like '%:parameter%'

I've read before that this would then make it impossible for the DB to use the company.name index.

My question then would be, what other options do I have to minimize the performance degradation that this will introduce to the search? Please note that, this is client requirements therefore I don't have the option to not implement (even though I explained to them the performance consequence of this).

The application is using Sybase SE 12.5.3 (based on the driver used in DBArtisan 8.5.5)

Answer 1

I don't know Sybase, but you could do something along the lines below:

• Create a second field, which is the reverse of the company name.
• Add an index on this new field

• Use the following where clause

  WHERE company_name like ':parameter%' or reverse_name like Reverse(parameter)+'%'

Hope this points you in a good direction