[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql
Subject:    Re: is there a way to optimize like '%..%' searches ?
From:       "Dan Buettner" <drbuettner () gmail ! com>
Date:       2006-06-30 21:40:03
Message-ID: c97ba7a80606301440m6194ea95y1b27fe0b6bd34ea8 () mail ! gmail ! com
[Download RAW message or body]

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@mbj.dk> 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)
> 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=mysql@progressive-comp.com


[prev in list] [next in list] [prev in thread] [next in thread]