What about partitioning the database?

On 7/1/06, Dan Buettner <[EMAIL PROTECTED]> wrote:

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 <[EMAIL PROTECTED]> 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/[EMAIL PROTECTED]


Reply via email to