Re: Performance in searchig data using "like"

On Tue, May 15, 2001 at 11:29:33PM -0500, Jorge Sarmiento wrote:
> I have a 2.5 million rows database, and when I do a search using "like" it
> tooks about a minute to give me a result.
>
> What can I do to speed up the answer? reestructurating it is not an option
> due the kind of data we have... more ram will help? using any kind of index?
>
> every suggestion will be welcome...

well, here's one: avoid the leading percent--

...where fld like 'this%'
-- CAN use index, since it's looking for strings BEGINNING with "this"

...where fld like '%that%'
-- CANNOT use index here, since it'll match "with that"
-- and "another that works" and "zoo creatures that sing"

and then, once you get your "like" searches to not use leading
%percent% marks, you may run into data dispersion trouble:
from what i've gleaned from this list, if you have 2 million
instances of "bubba" then there's not much gain to use an index
when you're searching for "bubba". but if you only have 2
hundred "gonzo" then it'll use the index when you ask to find
them.

--
What do I need manners for? I already got me a wife.
-- Adam Pontipee, "Seven Brides for Seven Brothers"

will(at)serensoft(dot)com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!