List:General Discussion« Previous MessageNext Message »
From:Dan Buettner Date:June 30 2006 9:40pm
Subject:Re: is there a way to optimize like '%..%' searches ?
View as plain text  
Wow, that is a tough one.

My question would be, how often is older data really accessed?  Could
you start incorporating a default date range like "past 3 months" or
"past 1 year" into all searches, but allow people to override it if
needed?  Then if you add an index on the timestamp column it would
help any searches with a date clause.

Dan


On 6/30/06, Martin Jespersen <mbj@stripped> wrote:
> It's basically a log that people needs to be able to search with
> wildcards in... the log grows many thousand records per day and never
> gets smaller, so searches just gets slower and slower. There is a sort
> field, the timestamp which is used in the searches, but it only makes
> the searches lsower yet instead of helping in the query, since all that
> does is sort by timestamp desc
>
>
> basically the query works like this:
>
> some searches for "foo bar baz" and i create an sql that looks like:
>
> select * from table where  logline like '%foo%bar%baz%' order by
> timestamp desc. I have wrekced my brian plenty but have not come up with
> any otehr way of doing it that gives the needed flexibility in the
> searces. Since what is searched for is not words as such - most loglines
> are actually a single "word" on the form
>
> "something<specialchar>something<specialchar>something<specialchar>something<specialchar>something<specialchar>"
> and so on - the logline is varibale length and variable number of
> "entities" between the sepcial chars (even the special chars are very
> varied) and of no specific format, thus the needed flexibility in the
> searches.
>
> If i coud i would changes the log format, but that is not possible since
> this database has loglines going all the way back to the 1980's (with
> more "old" lines being added as well as new ones) and the format has
> changed many times since then...
>
> Basically i am stuck with a very crappy heap of data i need to be able
> to search in a smart manner.
>
> Fulltext seaching would have been ideal if i was able to do boolean
> macthes with leading wildcard, but without it is useless :/
>
> btw the result doesn't need scoring for relevance at all - what is
> searched for is always the newest matches to the searchterm, regardless
> of relevance (relevance could become handy at a later stage tho, but i
> dare not even think about it atm)
>
Thread
is there a way to optimize like '%..%' searches ?Martin Jespersen28 Jun
  • Re: is there a way to optimize like '%..%' searches ?Dan Buettner28 Jun
    • Re: is there a way to optimize like '%..%' searches ?Peter Van Dijck28 Jun
    • Re: is there a way to optimize like '%..%' searches ?Martin Jespersen28 Jun
    • Re: is there a way to optimize like '%..%' searches ?Martin Jespersen29 Jun
      • Re: is there a way to optimize like '%..%' searches ?Dan Buettner30 Jun
        • Re: is there a way to optimize like '%..%' searches ?Martin Jespersen30 Jun
          • Re: is there a way to optimize like '%..%' searches ?Dan Buettner30 Jun
            • Re: is there a way to optimize like '%..%' searches ?Asif Lodhi1 Jul